Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using multiple where clauses with laravel query builder

I'm having a lot of trouble converting the following SQL query to work with Laravel's query builder.

SELECT * FROM gifts 
JOIN giftcategory ON gifts.id = giftcategory.giftid
JOIN giftoccasions ON gifts.id = giftoccasions.giftid
JOIN giftrelationship ON gifts.id = giftrelationship.giftid

WHERE (gifts.gender = 'any' OR gifts.gender = 'male')
AND giftoccasions.occasionid = '2'
AND (giftcategory.categoryid = '0' OR giftcategory.categoryid = '1')
AND giftrelationship.relationshipid = '1'

This query works fine, but I can't get the same results when using Laravel's query builder. I have the following code so far. It is not working correctly at all. I'm thinking the issue could lie with the orWhere part because it seems to be returning results that don't match any of the other where clauses.

$giftQuery = DB::Table('gifts')
->Join('giftcategory', 'gifts.id', '=', 'giftcategory.giftid')
->Join('giftoccasions', 'gifts.id', '=', 'giftoccasions.giftid')
->where('gifts.gender', '=', "male")
->orwhere('gifts.gender', '=', "any")
->where('giftoccasions.occasionid', '=', "2")
->where('giftoccasions.relationshipid', '=', "1")
->Where('giftcategory.categoryid', '=', "0")
->orWhere('giftcategory.categoryid', '=', "1");
like image 868
Dan Hastings Avatar asked Mar 30 '15 08:03

Dan Hastings


People also ask

Which is better eloquent or query builder in Laravel?

Eloquent ORM is best suited working with fewer data in a particular table. On the other side, query builder takes less time to handle numerous data whether in one or more tables faster than Eloquent ORM. In my case, I use ELoquent ORM in an application with tables that will hold less than 17500 entries.

How do I use whereBetween in Laravel?

The whereBetween() method is a query builder chained alongside other Laravel query builders used to fetch data from the database. The whereBetween() method queries the database table to fetch rows of records from the database within a range of values.

Does Laravel query Builder use PDO?

The Laravel query builder uses PDO parameter binding to protect your application against SQL injection attacks. There is no need to clean or sanitize strings passed to the query builder as query bindings.

Is Laravel query Builder an ORM?

An web application always needs to interact with a database and Laravel makes this task hassle free. A few tools that make Laravel an awesome framework is the inclusion of “Query Builder and Eloquent ORM”.


1 Answers

You want to use advanced where with parameter grouping:

$giftQuery = DB::table('gifts')
    ->join('giftcategory', 'gifts.id', '=', 'giftcategory.giftid')
    ->join('giftoccasions', 'gifts.id', '=', 'giftoccasions.giftid')
    ->where(function($query) {
        $query->where('gifts.gender', '=', "male")
            ->orWhere('gifts.gender', '=', "any");
    })
    ->where('giftoccasions.occasionid', '=', "2")
    ->where('giftoccasions.relationshipid', '=', "1")
    ->where('giftcategory.categoryid', '=', "0")
    ->orWhere('giftcategory.categoryid', '=', "1");
like image 189
Limon Monte Avatar answered Oct 10 '22 11:10

Limon Monte