Doubled subscriteria

BIZ TMS Aurelius
1

I have Tcustomer with SearchTerms property

    [ ManyValuedAssociation( [ TAssociationProp.Lazy], CascadeTypeAll, 'Fcustomer' ) ]
    [ ForeignJoinColumn( 'customer_id', [ TColumnProp.Required ] ) ]
    FSearchTerms: proxy< tlist< tCustomerSearchTerm > >;
    function GetSearchterms: tlist< tCustomerSearchTerm >;
...
property SearchTerms:tlist<TcustomerSearchterm>;

I want to search all customer containing both search terms "mika" and "koistinen"
in sql i would do it

select distinct f1, f2 ,,,, fN 
from 
  customers, searchterm as st1, searchterm as st2
where 
customer.id = st1.customer_id and 
customer.id = st2.customer_id and 
customer.id = st1.searchterm 
and st1.searchterm like 'MIKA%' 
and st2.searchterm like 'KOISTINEN%'

I tried to concatenate two subcriteria but last one refered of course to searchterm

  f := fom.Find< Tcustomer >.
    SubCriteria( 'searchterms' ) ///
      .where( Linq[ 'SearchTerm' ].Like( 'MIKA%' ) ) //
      .SubCriteria( 'searchterms' ) //
      .where( Linq[ 'SearchTerm' ].Like( 'KOISTINEN%' ) ) //
      .RemovingDuplicatedEntities
     .List<Tcustomer>;
2

Don't use SubCriteria. Use CreateAlias, in a similar way you did with SQL:

  f := fom.Find< Tcustomer >.
      .CreateAlias('SearchTerms', 'st1')
      .CreateAlias('SearchTerms', 'st2')
      .where( Linq[ 'st1.SearchTerm' ].Like( 'MIKA%' ) ) 
      .where( Linq[ 'st2.SearchTerm' ].Like( 'KOISTINEN%' ) ) 
      .RemovingDuplicatedEntities
     .List<Tcustomer>;
3

Unfortunatelly that produced sql

SELECT ...
FROM customers  A
  LEFT JOIN searchterm  B ON (B.customer_id = A.id 
WHERE  B.searchterm LIKE :p_0 AND 
B.searchterm LIKE :p_1``
(translated fields from finnish names, so might be minor typos :slight_smile:  )


I also noticed while playing with this that entities with inheritance 
than even fObjMgr.find<tBaseCustomer>.list; generates 
left join to inherited table. 
I'm sure that it has been working before summer...
4

Indeed, my mistake. That is not a valid Aurelius construction. You cannot have multiple aliases ("joins") to the same association, unfortunately. You will have to workaround that in a different way, maybe with direct SQL.

The joined-table inheritance have always used left join to bring descendant classes, that's how polymorphism is implemented.

5

That's pity. I'd like to implement advanced customer / product search service to xdata server and handle it with aurelius so output data would be similar to entity endpoint.

Are there any other options for this case.
So the goal is to create service
advancedcustomersearch(searchterms: string):tlist<tCustomer>;
searchterm might be "mika koistinen finland" and it would then return all customers who have all of those words in searchterms table.
I was thinking options like

  1. Can I create entities from dataset, for example FDQUery assuming that by query has all fields that entities require. Is there some lowerlevel function in aurelius which I could call

  2. Could I modify sql just before executing replace every starting from where

That left join was my mistake.. I must have remembered it wrongly

6
  1. That is unfortunately not currently possible, yet.
  2. Well, you can always use a SQL expression, which you can add anything you want. Maybe you can add a condition using EXISTS() or IN ()?

A third alternative is to query for TcustomerSearchterm object, then add each associated customer in a list making sure they are not duplicated - then return the list.

1 Like
7

Of course, I forgot linq.sql('A.customerid IN ('+ MyOwnSearchLogic+') ');
Thanks Wagner, again again... :slight_smile:

1 Like