Transforming SQL query to eloquent

I'm trying to transform some API endpoints that use SQL to something more eloquent. Right now, I have a series of API's that all are built on SQL which makes them a bit brittle.

I have an example. In a search box I'm looking for parents or students who have "ex" in their name.

Here is the sql

SELECT
    s.last_name,
    s.first_name,
    r.title,
    g.first_name,
    g.last_name
FROM
    students s
    INNER JOIN guardian_student gs ON s.id = gs.student_id
    INNER JOIN guardians g ON g.id = gs.guardian_id
    INNER JOIN rooms r ON r.id = s.room_id
WHERE
    s.first_name LIKE "%ex%"
    OR s.last_name LIKE "%ex%"
    OR g.first_name LIKE "%ex%"
    OR g.last_name LIKE "%ex%"

Structure

student (belongs to a) room (has many) student

student (belongs to many) guardians (belongs to many) student

all relationships are standard

This will return 1-2 tuples depending on the number of the students legal guardians.

Is there a way, to add a where clause on subquery

Something like

Student::with('guardians')
->where('first_name', 'like' '%'.$searchTerms.'%')
->orWhere('first_name', 'like' '%'.$searchTerms.'%')

// some magic here that lets me also check the guardians names against the search terms <---

->get();

Thanks

jceceniceros

6 years ago

Best Answer

Level 1

You can use the whereHas and orWhereHas methods to write a subquery on a relationship, something like this:

Student::with('guardians')
->where('first_name', 'like' '%'.$searchTerms.'%')
->orWhere('first_name', 'like' '%'.$searchTerms.'%')
->orWhereHas('guardians', function ($query) {
    $query->where('first_name', 'like' '%'.$searchTerms.'%')
});

You can find more examples at the official documentation:

https://laravel.com/docs/5.8/eloquent-relationships#querying-relationship-existence

Roni

6 years ago

Level 33

Thank you!

Please or to participate in this conversation.