CODEWITHSUNDEEP
phpmysqldatabaselaravel
Kaherdin
Kaherdin

Reputation: 2127

DB Query : Select row where and where

I want to make a search bar but limit the result to user sharing the same company. This function works fine :

public function search(Request $request) {
    if ($user = Sentinel::check()) {
        $users = User                
            ::where('first_name', 'like', '%' . $request->text . '%')
            ->orWhere('last_name', 'like', '%' . $request->text . '%')
            ->limit(2)
            ->get()
            ->map(function ($item) {
                $item['url'] = route('user.single', ['id' => $item->id]);
                $item['title'] = $item['first_name'] . ' ' . $item['last_name'];
                $item['type'] = 'User';
                return $item;
            })
            ->toArray();
    }
}

But I want to add the condition : 

->andWhere ('companies_id', 1)

Upvotes: 0

Views: 314

Answers (5)

Pramod Patil
Pramod Patil

Reputation: 2763

You can simply add where clause

public function search(Request $request) {
    if ($user = Sentinel::check()) {
        $users = User                
            ::where('first_name', 'like', '%' . $request->text . '%')
            ->where('companies_id', 1)
            ->orWhere('last_name', 'like', '%' . $request->text . '%')
            ->limit(2)
            ->get()
            ->map(function ($item) {
                $item['url'] = route('user.single', ['id' => $item->id]);
                $item['title'] = $item['first_name'] . ' ' . $item['last_name'];
                $item['type'] = 'User';
                return $item;
            })
            ->toArray();
    }
}

This will result in this query

WHERE `firstname` LIKE '%?%'
AND `company_id` = 1
OR `lastname` LIKE '%?%'

but I think it should be

WHERE `company_id` = 1
AND (
    `firstname` LIKE '%?%'
    OR `lastname` LIKE '%?%'
)

So these answer should be the right: https://stackoverflow.com/a/44281362/6193316

Upvotes: 1

Cool
Cool

Reputation: 159

It's Simple

$select = DB::('table_name')->where('column_name','value')
                            ->where('cloumn_name1','value')
                            ->get();

Upvotes: 0

Reena Mori
Reena Mori

Reputation: 645

public function search(Request $request) {
if ($user = Sentinel::check()) {
    $users = User                
        ::where('first_name', 'like', '%' . $request->text . '%')
       ->where('companies_id', 1) //you can add multiple
        ->orWhere('last_name', 'like', '%' . $request->text . '%')
        ->limit(2)
        ->get()
        ->map(function ($item) {
            $item['url'] = route('user.single', ['id' => $item->id]);
            $item['title'] = $item['first_name'] . ' ' . $item['last_name'];
            $item['type'] = 'User';
            return $item;
        })
        ->toArray();
}

}

Upvotes: 0

UfguFugullu
UfguFugullu

Reputation: 2147

I think your query should work fine with this

$users = User::where(function($query) use ($request) {
        $query->where('first_name', 'like', '%' . $request->text . '%')
            ->orWhere('last_name', 'like', '%' . $request->text . '%');
    })
    ->where('companies_id', '=', 1)
    ->limit(2)
    ->get()
    ->map(function ($item) {
        $item['url'] = route('user.single', ['id' => $item->id]);
        $item['title'] = $item['first_name'] . ' ' . $item['last_name'];
        $item['type'] = 'User';
        return $item;
    })
    ->toArray();

Upvotes: 0

Sina.Moradbakhti
Sina.Moradbakhti

Reputation: 51

you must use same structure for 'and' e.g : where('companies_id', '=', '1')

Upvotes: 0

Related Questions