1

This is my last attempt to create a complex query in Laravel. I have 3 tables I need for this scenario: photos, events, countries. Each event has many or none photos, each country may have multiple events with photos. My result shows 

PhotosByCountry, EventsByCountry, rContinent, rCountry.

And here is the well functioning MySQL native query:

SELECT SUM( allPhotos ) AS PhotosByCountry, 
 COUNT( temp.Land ) AS EventsByCountry, 
 rContinent, 
 temp.Land AS rCountry
FROM (
 SELECT e.country AS Land, COUNT( p.id ) AS allPhotos, c.continent AS rContinent
 FROM  `photos` p
 INNER JOIN  `events` e ON e.id = p.eventID
 INNER JOIN countries c ON e.country = c.country
 GROUP BY p.eventID
 )temp
GROUP BY rCountry

Who can help me translating this into Laravel Query builder without DB::raw() or whereRaw(). My main issue in building that thing is the sub query.

I got Models for all tables: Photo, Country, Sportevent (for table events (= legacy name), couldn't use Event).

Thanks for your efforts, I'm happy to provide additional info if needed.

Addition

Tables:

events

id | name        | country ... has more columns of course
1  | Eventname 1 | France
2  | Eventname 2 | Switzerland
3  | Eventname 3 | France

photos

id | eventID | path  ...
1  |   2     | .....
2  |   1     | .....
3  |   2     | .....
4  |   3     | .....
5  |   3     | .....
6  |   2     | .....

countries

id |  country      | continent (or geographical Region)  ...
1  |  France       | Europe
2  |  Switzerland  | Europe
3  |  Germany      | Europe
4  |  United States| North America
5  |  Australia    | Oceania
6  |   .....

Result

PhotosByCountry | EventsByCountry | rContinent | rCountry 
      3         |       2         | Europe     | France
      3         |       1         | Europe     | Switzerland
Edwin Krause
  • 1,766
  • 1
  • 16
  • 33
  • I don't think it's possible without DB::Raw. In this SO question they provide an anwser how to select from a sub-select. http://stackoverflow.com/questions/24823915/how-to-select-from-subquery-using-laravel-query-builder – blablabla Mar 11 '15 at 10:49
  • Does Laravel 5 have something for this? Must be a common issue... – Edwin Krause Mar 11 '15 at 11:18
  • It's an interesting article though. Considering that Eloquent Query Builder is supposed to make things easier for us, the valid answer there does everything but that. – Edwin Krause Mar 11 '15 at 11:22
  • An ORM only makes queries easier, where it is build for. Normally that is like 90% of all queries (wild guess). For everything more fancy you can always do a raw query. I used this in a Laravel project too, but I could make it easier by changing the table design. – blablabla Mar 11 '15 at 16:00
  • Can you post the tables, maybe 2 simple queries can do the same? – blablabla Mar 11 '15 at 16:26
  • I added the tables with example data and example result. I hope you give me an upvote for the extra work :-)... I need this query for this page: http://dev.hobbyathletes.com/event-photos . I'm working on this at the very moment, so the design looks a bit aweful at the moment – Edwin Krause Mar 11 '15 at 17:47
  • Why do you have the country column in the events table? Why not use a foreign key to the countries table (e.g. country_id)? Do you know for sure that your schema is well designed and normalized? Are all of your relations defined on your models? It might help to see your model code. Also, do you have an example of your attempt at writing the query using the Eloquent ORM? – dcarrith Mar 11 '15 at 18:35
  • Yes, using the id as foreign key would have been the better way in this case. But most of the time I leave the countries table untouched when using the events table. In addition, it's all modified legacy data. I could change that, but I think it's not really worth it. Models have pretty standard relations: `events ->hasOne('Country',...) ->hasMany('Photo',...) ` and `Photo ->hasOne('Sportevent')` and `Country has no relationships defined` – Edwin Krause Mar 11 '15 at 21:32

2 Answers2

1

Try this:

Country model

protected function photosCount()
{
    return $this->hasManyThrough('photos', 'events', 'country', 'event_id')
        ->groupBy('countries.country')
        ->count();
}

public function getPhotosCount() {
    return $this->photosCount ? $this->photosCount->count : 0;
}

protected function eventsCount()
{
    return $this->hasMany('events', 'country', 'event_id')
        ->groupBy('countries.country')
        ->count();
}

public function getEventsCount() {
    return $this->eventsCount ? $this->eventsCount->count : 0;
}

Access it like this:

$countries = Country::with('photosCount', 'eventsCount')->get();
$countries->first()->getPhotosCount();
$countries->first()->getEventsCount();

Code is untested.

Source: http://laravel.io/forum/05-03-2014-eloquent-get-count-relation

This will probably make 3 sql queries instead of 1 (one for the countries + 2 WHERE IN queries with the counts). But I thought it was a pretty nice way to get the counts.

blablabla
  • 1,468
  • 15
  • 17
  • `eventsCount()` doesn't work like this, as (like mentioned in initial question) not every event has photos. Perhaps I should have added an event without photos in the test data. `photoCount()` , well I'm too tired to analyze and test it now :-), but that could actually work. – Edwin Krause Mar 11 '15 at 21:40
  • Blablabla... thanks for the enormous effort. But meanwhile I have build already my page load and AJAX request based on the result the initial query produces. It would be too complicated to translate your code to get my result (which may work) into my page, unless I get the result in a format as in my question above. I give you an upvote for effort! :-) But I need to continue with the related work now. – Edwin Krause Mar 12 '15 at 13:02
  • Ok, no problem. I was interested in this myself. – blablabla Mar 12 '15 at 14:04
0

To complete the question:

Since it seems to either impossible or too complicated to build my initial query in Eloquent Query Builder I use the following function in my Photo Model (but I could have placed it in any other Model)

public function allCountrysWithPhotos(){
    $rawQuery=" SELECT SUM( allPhotos ) AS PhotosByCountry, 
                 COUNT( temp.Land ) AS EventsByCountry, 
                 rContinent, 
                 temp.Land AS rCountry
                FROM (
                 SELECT e.country AS Land, 
                  COUNT( p.id ) AS allPhotos, 
                  c.continent AS rContinent
                 FROM  `photos` p
                 INNER JOIN  `events` e ON e.id = p.eventID
                 INNER JOIN countries c ON e.country = c.country
                 GROUP BY p.eventID
                 )temp
                GROUP BY rCountry
                ORDER BY rContinent, rCountry";

    return DB::select($rawQuery);   
}

This works and is easy to understand for SQLers in case something needs to be adjusted in future (Index perhaps at some stage).

Anyway, Thanks for your efforts, I will not ask Eloquent questions anymore and will do it with native queries instead.

Edwin Krause
  • 1,766
  • 1
  • 16
  • 33