Search multiple table columns for the same value

Asked Jan 04 '22 at 09:51

Active Jan 04 '22 at 10:15

Viewed 52 times

I would like to replicate what our database search engine calls a 'multi-field search' in DQL (which is pretty much SQL as I understand).

I'm not sure if this is possible, but the syntax would look something like...

SELECT title, container_no, archive_status 
FROM record_table 
WHERE * like 'search term'

Can it be done? Surely there must be a better way than having a string of...

WHERE column1 = "search term" OR
column2 = "search term" OR
column3 = "search term" OR
etc...

If this were possible, I envisage it would look something like this:

WHERE (column1, column2, column3, column4) like '%search term%'

Edit 1: Updated pseudo code for clarity

Edit 2: Added additional thoughts

edited Jan 04 '22 at 10:15

asked Jan 04 '22 at 09:51

HotSauceCoconuts

1

Are you looking for `AND` oor `OR`? – D-Shih Jan 04 '22 at 09:57
Full-text search maybe ? – AymDev Jan 04 '22 at 09:59
Updated question: I was looking for OR! – HotSauceCoconuts Jan 04 '22 at 10:16
Problems like this often indicate a poor db design. – jarlh Jan 04 '22 at 10:33
Multiple *or* conditions are likely to not be performant and other approaches could be better. – Stu Jan 04 '22 at 10:35
Couldn't agree more, but that's what I've got to work with! – HotSauceCoconuts Jan 04 '22 at 10:43
@jarlh Can be old database design too, since they were much more limited by the amounts of characters used. – Tilen Jan 04 '22 at 11:02
@Tilen, in many cases such design wastes disk space. – jarlh Jan 04 '22 at 11:06
@jarlh Yes, which I never disagreed with. Old design is poor design, yet sometimes we have to work with it. – Tilen Jan 04 '22 at 11:35
You could create a DQL-Extension: https://www.doctrine-project.org/projects/doctrine-orm/en/2.9/cookbook/dql-custom-walkers.html Or just `x.prop1 = :search_term OR x.prop2 = :search_term ...` – goulashsoup Feb 05 '22 at 18:19

0 Answers0