How to reference columns to order a union query?

Question

I have done a Union in cakephp 3, however the union is doing it such that the buyers table records are shown first and then the tenants. Whereas i want it to be arranged based on created field in both Buyers and Tenants table so the latest records appears first. I tried doing that using epilog but gives an error

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'created' in 'order clause'

$tenant = $this->Tenants
    ->find('all', [
        'fields' => ['Tenants.id', 'Tenants.created', 'Consumers.type'],
        'conditions' => ['Tenants.client_id' => '1'],
        'order' => 'Tenants.created desc'
    ])
    ->contain(['Consumers']);

$buyer = $this->Buyers
    ->find('all', [
        'fields' => ['Buyers.id', 'Buyers.created', 'Consumers.type'],
        'conditions' => ['Buyers.client_id' => '1'],
        'order' => 'Buyers.created desc'
    ])
    ->contain(['Consumers']);

$results = $buyer->unionAll($tenant);
$results->epilog('ORDER BY created DESC LIMIT 7 OFFSET 7');

what is the relation between these 2 tables – Soubhagya Kumar Barik May 29 '19 at 12:25 — Soubhagya Kumar Barik, May 29 '19 at 12:25

score 0 · Answer 1 · answered May 23 '19 at 11:57

0

The high level ORM query builder will select columns using aliases, ie it will create SELECT clauses like this:

SELECT Tenants.id AS Tenants__id, Tenants.created AS Tenants__created, etc...

So your epilog snippet won't see a created column, hence the error. In such a case the most simple thing is to reference the column via its position number (though in at least MySQL that's deprecated IIRC!):

ORDER BY 2 DESC

Alternatively select the created column additionally with a common alias, like:

['Tenants.id', 'Tenants.created', 'created' => 'Tenants.created', 'Consumers.type']
['Buyers.id', 'Buyers.created', 'created' => 'Buyers.created', 'Consumers.type']

that will select the column like Tenants.created AS created, and you can reference it in your ORDER BY clause via created.

At least in MySQL it also seems to work to reference one of the aliases if the two queries are using different aliases for the same column (as union selects work on a position basis), but I'm not sure if this is true for other DBMS', anyways for the sake of completion, the following could work too:

ORDER BY Tenants__created DESC

answered May 23 '19 at 11:57

ndm

59,784
9
71
110

Pagination not working $results = $buyer->unionAll($tenant); $results->epilog('ORDER BY created DESC'); – Usman Saeed May 23 '19 at 12:34
@UsmanSaeed That's a different problem, see for example **https://stackoverflow.com/questions/29379579/how-do-you-modify-a-union-query-in-cakephp-3/29386189#29386189**, you'd have to use the union in a subquery. – ndm May 23 '19 at 12:38
Ok thanks, any idea what the code will be for that please? – Usman Saeed May 23 '19 at 13:59
@UsmanSaeed Please check the linked answer, it contains an example. – ndm May 23 '19 at 14:03
Tried but no luck with it – Usman Saeed May 23 '19 at 14:24
@UsmanSaeed I'm sorry but I can't really help with "_doesn't work_". If there's a specific problem with that new task, then I'd suggest that you create a new question, where you can explain the problem in detail and show your attempts / your code. – ndm May 24 '19 at 10:53
Added a new question: https://stackoverflow.com/questions/56291582/pagination-not-working-in-a-cakephp-3-union – Usman Saeed May 24 '19 at 11:18

How to reference columns to order a union query?

1 Answers1