sqflite: How to make a SELECT with two columns inside the same rawQuery

Question

I want to receive info from 2 columns with OR operator, but i can not visualice.

I have my column title and my column categoria. I want to make a select that affects both columns, something like this: selec * from title OR categoria where ...

this is what i have:

final List<Map<String, dynamic>> queryResult = await db
    .rawQuery('SELECT * FROM todos WHERE title like ?', ['%'+queryCourse+'%']); //here i want to complete the select with both columns

Answer 1

You want the UNION ALL operator.

Select lastName from Employees
UNION ALL
Select lastName from Patients

see

https://www.sqlitetutorial.net/sqlite-union/

Their example is:

SELECT FirstName, LastName, 'Employee' AS Type
FROM employees
UNION
SELECT FirstName, LastName, 'Customer'
FROM customers;

Note, the columns must "match". Here I use "lastName".

You cannot "union all" unlike columns. in the example from the sqlitetutorial, the third column is a "string"... it can have different value(s), BUT it must be the same "data-type" and ordinal position in the SELECT statement.

Be sure to read the documentation on the different between UNION and 'UNION ALL'.

For the "where" condition, you'll have to specify on each "part" of the UNION ALL

https://www.techonthenet.com/sqlite/union_all.php

SELECT department_id, department_name
FROM departments
WHERE department_id >= 10
UNION ALL
SELECT employee_id, last_name
FROM employees
WHERE last_name = 'Anderson'

;

Answer 2

Use the OR operator for the 2 columns:

final List<Map<String, dynamic>> queryResult = await db
    .rawQuery('SELECT * FROM todos WHERE title LIKE ? OR categoria LIKE ?', 
              ['%'+queryCourse+'%', '%'+queryCourse+'%']);

or:

final List<Map<String, dynamic>> queryResult = await db
    .rawQuery("SELECT * FROM todos WHERE title LIKE '%' || ? || '%' OR categoria LIKE '%' || ? || '%'", 
              [queryCourse, queryCourse]);

I assumed that for both columns you will use the same parameter queryCourse.
If not, then change the parameters with the ones you have.