XF 2.2 A slightly complex query with subqueries in finder

[Dannymh]

Hi all,

I am currently running this query with a fetchOne and I am wondering if there is anyway for me to wrap this into my finder so I can just query through the entity. I haven't had any luck trying to put the where clauses in.

Essentially I am looking for the row with the maximum amount in one column and the minimum amount in another. In plain english, the row that has the highest entry and the earliest date. "Amount" is the field that holds the amount we are checking and post_date holds the date int.

I believe my query should work for that, but just wanted to avoid raw queries where possible.

db()->fetchOne("SELECT * FROM `st_ledger` WHERE amount = (select max(amount) from st_ledger) order by post_time ASC limit 1");


Thanks
Dan

 

[Jeremy P]

You can use finder expressions:

$finder = \XF::finder('Some:Entity');
$finder
    ->where($finder->expression('%s = (SELECT MAX(amount) FROM st_ledger)', 'amount'))
    ->order('post_time')
    ->limit(1);