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
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...
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.
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
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
Could I modify sql just before executing replace every starting from where
That left join was my mistake.. I must have remembered it wrongly
That is unfortunately not currently possible, yet.
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.