Models Manager... how to BIND in ORDER BY?

Question

Models Manager... how to BIND in ORDER BY?

OakBehringer Jan '18

Created Jan '18	Last Reply Jan '18	Replies 2	Views 778	Votes 1

OakBehringer 4.2k

Jan '18

Need to somehow recreate this query in Phalcon, been stuck on it for hours:

select      aud.*
from        address_user_detail aud
inner join  user u on u.id = aud.user_id
left join   property_address pa on pa.id = aud.address_id
where       u.account_id = 11
        and (
                match(aud.label, aud.notes) against ('SOME STRING') 
                or match(pa.street, pa.city, pa.state, pa.zip) against ('SOME STRING')
        )
order by    match(aud.label, aud.notes) against ('SOME STRING') + match(pa.street, pa.city, pa.state, pa.zip) against ('SOME STRING')

I can't see how I can BIND 'SOME STRING' into the order by clause. Another option would be to put the match statements into the select clause and reference them in the roder by clause... but I get an error any time I try to use columns function from Query Builder, to add any columns other than aud.*

$builder = $this->di->get('modelsManager')->createBuilder();
$builder->addFrom('AddressUserDetail', 'aud');
$builder->innerJoin('User', 'u.id = aud.userId', 'u');
$builder->leftJoin('PropertyAddress', 'pa.id = aud.addressId', 'pa');
$builder->andWhere(
        'u.accountId = :acctId: or (aud.shared <> 0 and ash.userId = :authUid:)',
        [ 'acctId' => $acctId, 'authUid' => $authInfo->uid ]
);
$builder->andWhere(
        'FULLTEXT_MATCH(aud.label, aud.notes, :qStr:) or FULLTEXT_MATCH(pa.street, pa.city, pa.state, pa.zip, :qStr:)',
        ['qStr' => $qStr]
);

That works great, just can't get the results ordered the way I need.

Thanks! Adam

p.s. Yes I'm using the Incubator to get the FULLTEXT_MATCH function, and yes THAT works fine.

1

Emilio Degiovanni · Answer 1 · 2018-01-02T19:23:17-07:00

Hi @Oak what do you get if you do this var_dump($builder->getQuery()) and look the pre-builded sql query

OakBehringer · Answer 2 · 2018-01-02T22:14:56-07:00

OakBehringer
4.2k

Jan '18

How would I BIND the parameters to the ORDER BY clause to figure that out?

Jonathan Aaron Steel · Answer 3 · 2018-01-03T00:49:30-07:00

If I remember well, ORDER BY clause and bound parameters are not supported by PDO.

EDIT: try with this:

$order = 2;

$stmt = $db->prepare("SELECT field from table WHERE column = :param ORDER BY :order DESC");
$stmt->bindParam(':param', $is_live, PDO::PARAM_STR);
$stmt->bindParam(':order', $order, PDO::PARAM_INT);
$stmt->execute();

https://stackoverflow.com/questions/2542410/how-do-i-set-order-by-params-using-prepared-pdo-statement

Bottomline: you need pass the order of column insted of the name (see SO link above)

Of course this is raw PHP PDO example, you need to use it in Phalcon in standard way.

OakBehringer · Answer 4 · 2018-01-03T09:37:09-07:00

Right, I can execute a raw query no problemo.

But I want to get models back.

As an alternative, I can move the logic into the select....

select      aud.*, FULLTEXT_MATCH(aud.label, aud.notes, :qStr:) + FULLTEXT_MATCH(pa.street, pa.city, pa.state, pa.zip, :qStr:) as rank
...
order by rank desc

BUT - I believe it's not possible to get models back when adding silly columns like that to the select. Is this correct?

Thanks!