[FIXED] How get a Value even if result is not found in the DB mysql

Issue

I need to find the right query to get a value even if the data not exist in the db.

I have my table named "prova":

it	en	de
data	data
riga	row	linie
parola

If I query:

SELECT en,de FROM `prova` WHERE `it` IN ("data","riga");

The resoult is:

en	de
data
row	linie

And it's ok!

BUT

SELECT en,de FROM `prova` WHERE `it` IN ("data","riga","ciao","parola");

The resoult is:

en	de
data
row	linie

Is as expected in the standard mysql but for me is a problem.

The the desired result is:

en	de
data	not found
row	linie
not found	not found
not found	not found

Why I want this?

I need to perform a query with an array of string, actually I need to query one by one string, and check if the query is empty. The size and the order of the resulted array should be equal to the given array.

Actually I need to perform 8700 queries, this will help me to decrease the queries number to 8.

Thanks

Solution

You can use a cte that returns the array of strings with a number that corresponds to the order of the string in the results and a LEFT join of the table:

WITH cte(id, word) AS (VALUES
  ROW(1, 'data'), ROW(2, 'riga'), ROW(3, 'ciao'), ROW(4, 'parola')
)
SELECT COALESCE(p.en, 'not found') en,
       COALESCE(p.de, 'not found') de
FROM cte c LEFT JOIN prova p
ON p.it = c.word
ORDER BY c.id;

Or, with UNION ALL for versions of MySql prior to 8.0 without the cte support:

SELECT COALESCE(p.en, 'not found') en,
       COALESCE(p.de, 'not found') de
FROM (
  SELECT 1 id, 'data' word UNION ALL
  SELECT 2, 'riga' UNION ALL 
  SELECT 3, 'ciao' UNION ALL 
  SELECT 4, 'parola'
) t
LEFT JOIN prova p ON p.it = t.word
ORDER BY t.id;

See the demo.

Answered By - forpas

Answer Checked By - David Goodson (PHPFixing Volunteer)

Read More

[FIXED] Why does a UNION ALL query treat the outer ORDER column as unknown?

Issue

I'm using unionAll() and return the data perfectly, but I need ordernate the data and always return error because the column not exists.

$events = $this->Events
    ->find('available')
    ->where([
        'Events.group_of_event_id IS NULL'
    ])
    ->select('Events.id')
    ->select('Events.name')
    ->select('Events.slug')
    ->select('Events.date_event_start')
    ->select([
        'is_group' => 0
    ]);

$groups = $this->GroupOfEvents
    ->find('available')
    ->select('GroupOfEvents.id')
    ->select('GroupOfEvents.name')
    ->select('GroupOfEvents.slug')
    ->select('GroupOfEvents.date_event_start')
    ->select([
        'is_group' => 1
    ]);

$limit = 10;
$page = 1;

if($this->request->query('limit'))
    $limit = $this->request->query('limit');

if($this->request->query('page'))
    $page = $this->request->query('page');

$offset = ($page - 1) * $limit;

$connection = ConnectionManager::get('default');
$union = $events->unionAll($groups)->epilog(
    $connection
        ->newQuery()
        ->order(['date_event_start' => 'ASC'])
        ->limit($limit)
        ->offset($offset)
);

Return this error:

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

Solution

As the error states, there is no date_event_start column.

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.

See also

• Cookbook > Database Access & ORM > Query Builder > Selecting Data

Answered By - ndm

Read More