Get rows based on different column in single query using Laravel Eloquent

Question

Let's say I have a table that looks like this:

name | link
-----------
 A   | asdf 
 A   | zxcv
 B   | qwer
 B   | rtyu
 C   | fghj

I am currently getting the results using 2 queries like the following using the $link variable:

// first query to get the row so I have the name
$m = Model::where('link', '=', $link)->get();    

// using the name, I get the rows I need
$results = Model::where('name', '=', $m->name)->get();

How can I do this in a single query?

what's the use case you have? Is the $link submitted from a form or something? — Ali
– Ali, Commented Aug 1, 2017 at 17:59

Dan · Accepted Answer · 2019-05-07 14:02:57Z

3

You can use Builder::whereIn and subqueries to achieve what you want:

Model::whereIn("name", function ($query) use ($link) {
    $query->select("name")
        ->from((new Model)->getTable())
        ->where("link", $link);
})->get();

edited May 7, 2019 at 14:02

answered Aug 1, 2017 at 18:01

Dan

5,3782 gold badges21 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Howard Over a year ago

If I wanted to do a "leftJoin" on the "with(new Model)" part, how would I go about doing this?

Zayn Ali · Accepted Answer · 2017-08-01 19:37:32Z

1

Try this

$results = Model::whereIn('name', function($query) use ($link) {
    $query->from((new Model)->getTable())
          ->where('link', $link)
          ->select('name');
})->get();

OR with DB (update the table_name with real one)

$results = DB::select(
    "SELECT * FROM `table_name` WHERE `name` IN (SELECT `name` FROM `table_name` WHERE `link` = :link)", 
    [ "link" => $link ]
);

edited Aug 1, 2017 at 19:37

answered Aug 1, 2017 at 18:16

Zayn Ali

4,9351 gold badge32 silver badges41 bronze badges

Collectives™ on Stack Overflow

Get rows based on different column in single query using Laravel Eloquent

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related