0

I want to move a raw mysql query into Laravel 4's query builder, or preferably Eloquent.

The Setup

  • A database for storing discount keys for games.
  • Discount keys are stored in key sets where each key set is associated with one game (a game can have multiple keysets).
  • The following query is intended to return a table of key sets and relevant data, for viewing on an admin page.
  • The 'keys used so far' is calculated by a scheduled event and periodically stored/updated in log entries in a table keySetLogs. (it's smart enough to only log data when the count changes)
  • We want to show the most up-to-date value of 'keys used', which is a 'greatest-n-per-group' problem.

The Raw Query

SELECT
    `logs`.`id_keySet`,
    `games`.`name`,
    `kset`.`discount`,
    `kset`.`keys_total`,
    `logs`.`keys_used`
FROM `keySets` AS `kset`
INNER JOIN
(
    SELECT
        `ksl1`.*
    FROM `keySetLogs` AS `ksl1`
    LEFT OUTER JOIN `keySetLogs` AS `ksl2`
        ON (`ksl1`.`id_keySet` = `ksl2`.`id_keySet` AND `ksl1`.`set_at` < `ksl2`.`set_at`)
    WHERE `ksl2`.`id_keySet` IS NULL
    ORDER BY `id_keySet`
)
AS `logs`
    ON `logs`.`id_keySet` = `kset`.`id`
INNER JOIN `games`
    ON `games`.`id` = `kset`.`id_game`
ORDER BY `kset`.`id_game` ASC, `kset`.`discount` DESC

Note: the nested query gets the most up-to-date keys_used value from the logs. This greatest-n-per-group code used as discussed in this question.

Example Output:

+-----------+-------------+----------+------------+-----------+
| id_keySet | name        | discount | keys_total | keys_used |
+-----------+-------------+----------+------------+-----------+
|         5 | Test_Game_1 |   100.00 |         10 |         4 |
|         6 | Test_Game_1 |    50.00 |        100 |        20 |
|         3 | Test_Game_2 |   100.00 |         10 |         8 |
|         4 | Test_Game_2 |    50.00 |        100 |        14 |
|         1 | Test_Game_3 |   100.00 |         10 |         1 |
|         2 | Test_Game_3 |    50.00 |        100 |         5 |
...

The Question(s)

I have KeySet, KeySetLog and Game Eloquent Models created with relationship functions set up.

  1. How would I write the nested query in query builder?
  2. Is it possible to write the query entirely with eloquent (no manually writing joins)?
Community
  • 1
  • 1
Johannes
  • 6,232
  • 9
  • 43
  • 59
  • Is the problem that you cannot use subqueries? – Strawberry Sep 02 '13 at 12:31
  • I don't know, that's what I'm asking :) I don't know if you can use subqueries like this. looking at the query builder documentation it seems that it doesn't support something like `->join(DB::RAW())`, so this may not be possible, and I'll end up having to hard-code it. But basically I was hoping someone with more Laravel expertise could provide some insight into how they would approach this. – Johannes Sep 02 '13 at 12:35

1 Answers1

0

I don't know Laravel or Eloquent so I probably shouldn't comment, but if performance isn't at stake then it seems to me that this query could be rewritten something like this:

 SELECT ksl1.id_keySet
      , g.name
      , k.discount
      , k.keys_total
      , ksl1.keys_used
   FROM keySetLogs ksl1
   LEFT 
   JOIN keySetLogs ksl2
     ON ksl1.id_keySet = ksl2.id_keySet
    AND ksl1.set_at < ksl2.set_at
   LEFT
   JOIN keysets k
     ON k.id = l.id_keySet
   LEFT
   JOIN games g
     ON g.id = k.id_game
  WHERE ksl2.id_keySet IS NULL
  ORDER 
     BY k.id_game ASC
      , k.discount DESC
Strawberry
  • 33,750
  • 13
  • 40
  • 57
  • The `ON k.id - l.id_keySet` line seems like it might not be quite right, not sure what you meant there. Also unfortunately performance is pretty important for the scale of this project. – Johannes Sep 02 '13 at 13:21