S
S
Sergey Shevchenko2017-01-09 18:39:24
MySQL
Sergey Shevchenko, 2017-01-09 18:39:24

PostgreSQL. WHERE cell contains one of the array values. How to make a request?

there is a field of type VARCHAR[]
it has {metro1,metro2, ... metroN}
there is a php array of type

$arr = [
    metro3, metro10, metro11
];

you need to find all the addresses where in the table cell there is at least one metro from the php array (from the example above, you need to select everyone who has metro3, metro10 and metro11 in the VARCHAR array)
Here is how I try to make this selection (the code does not work )
$sql = '
SELECT * FROM offer 
WHERE IN('.implode($arr).') @> offer.metro';

 // если вместо IN вставить одно значение, например 'metro1' то все норм
$sql = '
SELECT metro
FROM offer
WHERE metro @> '{Шаболовская}'';

// если сделать так то выберет именно те метро которые нужны, но только если у этого offer`a именно одно (искомое) метро а не несколько
$sql = '
SELECT metro
FROM offer
WHERE metro IN('{Шаболовская}','{Римская}','{Савеловская}','{Шаболовская}')';

// запрос выше выберет вот что:
{Шаболовская}
{Савеловская}
{Римская}

And if the offer has two subways (one of which is the desired one and the other is not found)
{Shabolovskaya,OtherWhat},
then such a user will not be included in the selection,
but if the user just has one subway (and it is the desired one),
for example {Shabolovskaya}
and we we are looking for Shabolovskaya
, then such a user will be included in the sample.
How to build a query so that all users are included in the selection if he has at least one desired metro (the searched metros are in the php array)?

Answer the question

In order to leave comments, you need to log in

1 answer(s)
M
Melkij, 2017-01-09
@lancer_serega

select ... from tablename 
where arraycolumn && array['foo', 'bar']::text[]

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question