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.
p.s. Yes I'm using the Incubator to get the FULLTEXT_MATCH function, and yes THAT works fine.