$events = $this->Events
'Events.group_of_event_id IS NULL'
'is_group' => 0
$groups = $this->GroupOfEvents
'is_group' => 1
$limit = 10;
$page = 1;
$limit = $this->request->query('limit');
$page = $this->request->query('page');
$offset = ($page - 1) * $limit;
$connection = ConnectionManager::get('default');
$union = $events->unionAll($groups)->epilog(
->order(['date_event_start' => 'ASC'])
Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'date_event_start' in 'order clause'
As the error states, there is no
Unlinke normale SQL queries, where a non-table prefixed column would fall back to referring to one of the involved tables, similar doesn't happen for union results, with union results you have to explicity refer to the columns as they have been selected.
So you have to make sure that either the columns are selected without a table prefix, or to select and use proper aliases in the
ORDER clause. In order to avoid ambiguity, I'd strongly suggest going for the latter, something like
->select(['date_event_start_alias' => 'Events.date_event_start']) // ... ->select(['date_event_start_alias' => 'GroupOfEvents.date_event_start']) // ... ->order(['date_event_start_alias' => 'ASC'])
It should be noted that at least with MySQL and Postgres (I'm not sure about other DBMS like SQLite or SQL Server), you actually have to set the alias only for the first
SELECT. Setting it for all selects won't do any harm, so I'm including it in the example, but it's not actually necessary.