Laravel DB::table AND statement?

Question

I'm very new to Laravel and don't quite understand the DB::table method in conjuction with an AND clause.

So I have this query at the moment:

$query = DB::select( DB::raw("SELECT * FROM tablethis WHERE id = '$result' AND type = 'like' ORDER BY 'created_at' ASC"));

It is working, but I'd like to use Laravel's Query Builder to produce something like:

$query = DB::table('tablethis')->where('id', '=', $result)->where('type', '=', 'like')->orderBy('created_at', 'desc')

But this seems to ignore the second where() completely. So, basically how do I make this work?

score 5 · Accepted Answer · answered Feb 12 '15 at 15:08

5

Just adding another ->where behind another ->where is the way to get another where statement

So your code should be fine.

$query = DB::table('tablethis')->where('id', '=', $result)->where('type', '=', 'like')->orderBy('created_at', 'desc')

There must be something wrong with a different part. I do hope this isn't your full query.

If it is, you need a ->get() at the end.

answered Feb 12 '15 at 15:08

Loko

6,539
14
50
78

I'll try to add the ->get() if it would help. Thanks :) – EmJeiEn Feb 12 '15 at 15:27
@user2300189 No problem. Let me know if it changed anything. Also if you do fix it, let me know what fixed your problem. – Loko Feb 12 '15 at 15:28
Unfortunately it didn't help. From some reason (if you check my comment to another answer) it just doesn't pick up the other where-> statement. Checked the logs for that. Maybe there's something wrong with the laravel installation I'm working with. So I suppose I do this just via raw method this time. Thank you in any case and sorry I can't give you an upvote as I don't have enough rep :) – EmJeiEn Feb 12 '15 at 17:47
@user2300189 Im very sorry to hear. Im using the query builder and it's working fine for me.(With 2 where clauses). There's nothing wrong with your code. The only thing I can think of now is check if the where clause is 100% right. See if the query doesn't contain anything unusual. Check if you're sure. Maybe using `dd(DB::getQueryLog());` to see what the query looks like raw. – Loko Feb 13 '15 at 07:44
Yeah, it's definitely 100% correct but the output is just wrong. There's something wrong with the LV installation I'm working with. I noticed that validator's rule alpha_num is also not working, whereas email validator rule is. I think I look into upgrading the whole thing into version 5. I did this with raw query however and it works just fine now. Thanks again! – EmJeiEn Feb 13 '15 at 13:47

score 2 · Answer 2 · answered Feb 12 '15 at 15:15

2

For sure this code should work as expected.

I don't know how you check the query executed, but you can go to app/providers/EventServiceProvider.php (I assume you have L5) and in boot method add:

Event::listen(
    'illuminate.query',
    function ($sql, $bindings, $time) {
        $sql = str_replace(array('%', '?'), array('%%', "'%s'"),
            $sql);
        $full_sql = vsprintf($sql, $bindings);

        file_put_contents(storage_path() . DIRECTORY_SEPARATOR .

            'logs' . DIRECTORY_SEPARATOR . 'sql_log.sql',
            $full_sql . ";\n", FILE_APPEND);
    }
);

to see exact SQL that was executed.

In addition (but it's just improvement) when using = operator, you can omit it, so you can use here:

$query = DB::table('tablethis')->where('id', $result)->where('type', 'like')->orderBy('created_at', 'desc');

answered Feb 12 '15 at 15:15

Marcin Nabiałek

109,655
42
258
291

Thanks for the info. And that omit looks like something what I'm going to use from now on :). Actually running Laravel 4.1.28, is that way working in it as well to see the executed SQL? – EmJeiEn Feb 12 '15 at 15:25
@user2300189 If you are new to Laravel you should consider learning L5 at the moment. You can add exact same code into `app/start/local.php` to track code change (it works in L4.2 - I haven't used L4.1 but it probably will work the same) – Marcin Nabiałek Feb 12 '15 at 15:27
Had to actually do this: http://stackoverflow.com/questions/19131731/laravel-4-logging-sql-queries (linking if someone has the same problem) to get the logging to work. And indeed it seems that it just doesn't run the other where-> from some reason. I would have started with laravel 5 as you suggested but I'm helping out a friend with her site which is built with laravel so unfortunately no chance for this project. Thanks for the help in any case, would upvote if I could :) – EmJeiEn Feb 12 '15 at 17:43

Laravel DB::table AND statement?

2 Answers2