Convert MySQL search for a pair of values in a table to Eloquent query

Question

I am having trouble reproducing this kind of a MySQL query using Eloquent

SELECT  *
    FROM    foo
    WHERE   (column1, column2) IN (('foo', 1), ('bar', 2))

There is a method in Eloquent query builder called whereIn(), but it can receive just one column as a parameter:

/**
 * Add a "where in" clause to the query.
 *
 * @param  string  $column
 * @param  mixed   $values
 * @param  string  $boolean
 * @param  bool    $not
 * @return $this
 */
public function whereIn($column, $values, $boolean = 'and', $not = false)
{ 
   ...
}

So, you can't do something like this

$qb = $this->model->whereIn(['column1', 'column2'], array([1, 2], [1,3], [3, 32]));

I am currently working very hard trying to find solution, but if anyone can help, I would be very grateful :)

EDIT: I managed to do it this way:

/**
 * ...
 */
public function findConnectionsByUser(User $user, array $userConnectionIds)
{
    $qb = $this->model->query();

    ...

    return $this->createQueryBuilderForUserConnectionsWithUserIds($qb, $user, $userConnectionIds)->get();
}

/**
 * @param Builder $qb
 * @param User    $user
 * @param array   $userConnectionIds
 *
 * @return Builder
 */
private function createQueryBuilderForUserConnectionsWithUserIds(Builder $qb, User $user, array $userConnectionIds)
{
    foreach ($userConnectionIds as $userConnectionId) {
        $qb->orWhere(array(
            array('receiver_id', $user->id),
            array('initiator_id', $userConnectionId)
        ))
            ->orWhere([
                ['receiver_id', $userConnectionId],
                ['initiator_id', $user->id]
            ]);
    }

    return $qb;
}

EDIT 2 (more scalable solution):

$qb = $this->model->query();
$oneSide = $this->model->newQuery()->where('receiver_id', '=', $user->id)
            ->whereIn('initiator_id', $userConnectionsIds);

return $qb->where('initiator_id', '=', $user->id)
            ->whereIn('receiver_id', $userConnectionsIds)
        ->union($oneSide)->get();

score 2 · Accepted Answer · answered Mar 08 '17 at 23:45

2

You can try to nest them like

$qb = $this->model->whereIn('column1', [1, 2, 3])
->orWhere(function ($query) {
    $query->whereIn('column2', [2, 3, 32]);
});

Also not sure if you could simply use to whereIn like this. Just try first

$qb = $this->model->whereIn('column1', [1, 2, 3])
->whereIn('column2', [2, 3, 32]);

The first query reads more like OR and the second more like an AND query.

answered Mar 08 '17 at 23:45

EddyTheDove

12,979
2
37
45

I managed to solve my problem before I saw your answer. But, for small test, I adapted your code for my purposes, and it seems to be working, Tnx! – Matko Đipalo Mar 12 '17 at 18:27

score 0 · Answer 2 · answered Mar 08 '17 at 18:20

0

It's a bit crude, but you could concatenate the columns to one, separating the two with some otherwise unused character like '|'.

answered Mar 08 '17 at 18:20

Koen

278
1
7

Hmm.. I don't think this could help. – Matko Đipalo Mar 12 '17 at 18:15

Convert MySQL search for a pair of values in a table to Eloquent query

2 Answers2