DomSinclair's avatar

Creating Eloquent Queries

I'm trying to get a better understanding of the way the Eloquent queries are / should be constructed. I'm very comfortable writing plain sql queries but then translating them to eloquent syntax is proving a challenge. Granted this is all new stuff to me so I understand that there is a leraning curve involved.

I have the following SQL query;

SELECT
  rescue_centres.name AS Centre,
  Count(DISTINCT animals.id) AS Total,
  animals.breed,
  animal_types.name AS type,
  animals.gender
FROM
  rescue_centres
  INNER JOIN animals ON animals.rescue_centre_id = rescue_centres.id
  INNER JOIN animal_types ON animal_types.id = animals.animal_type_id
GROUP BY
  rescue_centres.name,
  animals.breed,
  animal_types.name,
  animals.gender

In my Laravel app there are the following corresponding Models; RescueCentre, Animal, AnimalType.

I have a couple of questions if I may.

What is the best way to represent multiple joins like this in Eloquent syntax, and specify the specific columns one wants returned.

Probably the more important question in many respects if one is to learn this, which section of the Laravel Documentation would have been the one I should have turned to first in order to try and answer the first question. I find it a great shame that the examples shown don't have a plain SQL example alongside, it would make it much easier to pickup if they did.

0 likes
3 replies
martinbean's avatar

@domsinclair That query makes no sense. If you’re trying to fetch rescue centres, then why are you then selecting animals.breed etc? A rescue centre doesn’t have an “animal breed”, nor an “animal type”, nor an “animal gender”.

I think you need to think about your models a bit more and work out what it is you’re actually trying to query for.

DomSinclair's avatar

@martinbean The query makes absolute sense assuming of course that you know how to read sql in the fist place.

It returns a list showing what type and breed of animal (delineated by gender) is available at the rescue centres, exactly the type of thing that a prospective adoptee would want to know.

jlrdw's avatar

@DomSinclair I would suggest use your current working queries. Eloquent has to convert to normal sql at runtime anyway.

But there is a difference in:

Related data and Joins with grouping in eloquent.

Please or to participate in this conversation.