CODEWITHSUNDEEP
phpmysqllaravel
Rohit Thakur
Rohit Thakur

Reputation: 21

A working query returning null when run through laravel

I am trying to use a simple query with laravel but its not returning any data. but when I run same query in phpmyadmin it returns 3 rows.

This is code in laravel.

 $pages = DB::table('static_pages')
     ->where('slug','=','(select slug from static_pages where id='.$id.')')
         ->get();

this is the query it makes.

  select * from `static_pages` where `slug` = (select slug from static_pages where id=2)

Can you tell me what could be the reason?

Upvotes: 1

Views: 132

Answers (4)

omitobi
omitobi

Reputation: 7334

If what you refer to is the relationship between two tables then you might want to look at Laravel Model Relationship, however if you want to retrieve all records having the same slug of the record with id = 2 then you can also do it without using two DB:raw:

DB::table('static_pages')->where('slug', function ($query) {
        return $query->from('static_pages')->where( 'id', '2')->select('slug');
    })->get();

If you want to inspect the sql query it generates then use toSql() instead of ->get()

One evil thing to anticipate is if your inner query returns a collections of values then your comparison might become questionable. However since the field you use to make the query is unique, then this should not be a problem.

Hope this is useful.

Upvotes: 3

Rohit Thakur
Rohit Thakur

Reputation: 21

It worked by changing from this 

 $pages = DB::table('static_pages')->where('slug','=','(select slug from static_pages where id='.$id.')')->get();

to this 

$pages = DB::table('static_pages')->where('slug','=',DB::table('static_pages')->where('id','=',$id)->pluck('slug'))->get();

thanks to jishad

Upvotes: 1

HSLM
HSLM

Reputation: 2012

you can do this

 $pages = DB::table('static_pages')->where('slug','=',DB::raw('(select slug from static_pages where id='.$id.')'))->get();

Upvotes: 0

Gaurav Gupta
Gaurav Gupta

Reputation: 1698

try this one

DB::table('static_pages')->whereRaw("`slug` = (select slug from static_pages where id=2)")->get();

hope it will help you!

Upvotes: 0

Related Questions