Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

laravel mysql query with multiple where orwhere and inner join

Tags:

php

mysql

laravel

How can I prepare the following MySQL query in Laravel?

This is the code I used without success:

$user_list = DB::table('users')
->where('users.user_id' ,'=', $clientId)
->where('users.firstname', 'LIKE', '%'.$_POST['name'].'%')
->orWhere('users.lastname', 'LIKE', '%'.$_POST['name'].'%')
->orWhere('users.email', 'LIKE', '%'.$_POST['name'].'%')
->join('users_roles', 'users.id', '=', 'users_roles.user_id')                        
->where('users_roles.role_id', '=', Role::USER_PARTICIPANT)
->get();

The conditions must be:

  1. users.user_id == $clientId
  2. users.firstname == '%'.$_POST['name'].'%' OR users.lastname == '%'.$_POST['name'].'%' OR users.email == '%'.$_POST['name'].'%'
  3. inner join between users_roles and users crossing by users.id == users_roles.user_id when users_roles.role_id == Role::USER_PARTICIPANT
like image 457
Alberto Avatar asked Nov 21 '14 14:11

Alberto


People also ask

How to write two where condition in Laravel?

Laravel multiple where conditions - [OR]: It has to be regular where(). Syntax: ... ->where('column','operator','value') ->orWhere('column','operator','value') ...

How to use multiple or condition in Laravel query?

Laravel multiple where conditions - [OR]: In case you want your clauses to be joined with OR rather than AND , you could use the orWhere method: Syntax: ... ->where('column','operator','value') ->orWhere('column','operator','value') ...

Where IS NOT NULL eloquent?

Check if not null: whereNotNullSELECT * FROM users WHERE last_name IS NOT NULL; The equivalent to the IS NOT NULL condition in Laravel Eloquent is the whereNotNull method, which allows you to verify if a specific column's value is not NULL .

How add join in laravel?

We first get users , which is our primary table, i.e., the table that relates to the other tables we want to join. Then, we chain users to the join() method. In this case, the first parameter or table we want to join to the users table is the contacts table. 'users.id', '=', and 'contacts.


1 Answers

Okay it looks like your problem is with the nested where

Try this one:

$name = $_POST['name']; // I'd recommend you use Input::get('name') instead...

$user_list = DB::table('users')
    ->where('users.user_id' ,'=', $clientId)
    ->where(function($query) use ($name){
        $query->where('users.firstname', 'LIKE', '%'.$name.'%');
        $query->orWhere('users.lastname', 'LIKE', '%'.$name.'%');
        $query->orWhere('users.email', 'LIKE', '%'.$name.'%');
    })
    ->join('users_roles', 'users.id', '=', 'users_roles.user_id')
    ->where('users_roles.role_id', '=', Role::USER_PARTICIPANT)
    ->get();
like image 138
lukasgeiter Avatar answered Sep 28 '22 04:09

lukasgeiter