How to convert this SQL statement to laravel code?

Question

So I have two tables Animals and Adoptions

The animal table holds [animalID, name, DOB]

Adoptions hold [id, animalID, userID, approved]

I need to write a statement which would match the animalID in both the tables and would return the name and DOB of the animal where approved is 0.

I have hardly any previous experience with either mySQL or Laravel so my assumption would be that the SQL query would look like this:

SELECT animalName, DOB
FROM animal
INNER JOIN
Adoption ON adoption.animalID = animal.animalID
WHERE 
Adoption.approved = 0

Currently I am returning all of the animals in the table. So I need to add the join and do the checking to see if the animalID in the adoptions table has a value of 0

public function available  ()
{
    //JOIN ANIMAL AND ADOPTION TABLES
    //RETURN ALL ANIMALS WHERE APPROVED = 0


    $animalQuery = Animal::all();



    return view('/available', array('animals'=>$animalQuery));
}

Are you using eloquent models?

thisiskelvin
– thisiskelvin

2019-04-18 14:57:51 +00:00
Commented Apr 18, 2019 at 14:57 — thisiskelvin
– thisiskelvin, Commented Apr 18, 2019 at 14:57
yes @thisiskelvin

Marius Kuzm
– Marius Kuzm

2019-04-18 14:59:07 +00:00
Commented Apr 18, 2019 at 14:59 — Marius Kuzm
– Marius Kuzm, Commented Apr 18, 2019 at 14:59

Marius Kuzm · Accepted Answer · 2019-04-18 15:31:30Z

1

public function available  ()
{
    //JOIN ANIMAL AND ADOPTION TABLES
    //RETURN ALL ANIMALS WHERE APPROVED = 0


    $animalQuery = Animal::select('animals.id','animals.name', 'animals.DOB')
        ->join('adoptions', 'adoptions.animalid', '=', 'animals.id')
        ->where('adoptions.approved', 0)
        ->get();



    return view('/available', array('animals'=>$animalQuery));
}

This is my answer which worked for me

answered Apr 18, 2019 at 15:31

Marius Kuzm

1591 silver badge10 bronze badges

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

Comments

Justin Kruskie · Accepted Answer · 2019-04-18 15:01:21Z

First of all, you would need two models. An Animal model and an Adoption model. Laravel uses models to access your database table.

Using the Animal model you could create a query like this:

\App\Animal::with('adoption')->select('name','dob')->get();

Then inside your Animal model, you would need to create the relationship between the animal and adoption:

public function adoption()
{
    return $this->hasOne('App\Animal', 'animalID', 'animalID');
}

This would be one way to get an animal, with the adoption information.

I really recommend reading through the Laravel documentation for relationships and eloquent, and possibly read up on some SQL.

https://laravel.com/docs/5.8/eloquent

https://laravel.com/docs/5.8/eloquent-relationships

thisiskelvin · Accepted Answer · 2019-04-18 15:01:48Z

0

Assuming you have an Adoption model, and adoption relation on your Animal model, you can do the following:

$animals = Animal::select('animalName`, `DOB`)
    ->with(['adoption' => function($query) {
        $query->where('approved', false);
    })->get();

answered Apr 18, 2019 at 15:01

thisiskelvin

4,2021 gold badge12 silver badges18 bronze badges

Collectives™ on Stack Overflow

How to convert this SQL statement to laravel code?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related