Subqueries and the SQL Builder

hgtonight

I am developing a new application and I am running into a brick wall in
terms of subqueries. I don't really want to divulge the details as this
is a private job, but I will try and explain things as clearly as
possible.

I have two tables. The first table defines a game. The second table
defines a widget. Each Game has 0 or more widgets and each widget has 1
or more players. The issue I am having is getting all the Widget data as
well as the number of players using the widget given a game.

I ended up with something like this:

SELECT *, (SELECT
  COUNT(*)
  FROM `yarbs_widgets` AS w
  WHERE w.`GameID` = :id AND w.WidgetID = g.WidgetID)
  AS 'TeamSize'
  FROM `yarbs_games` AS g

This gives me a nice little readout like so:

| WidgetID | Name | Description | Value | TeamSize | 
----------------------------------------------------
| 1        | Ball | A ball      | 2     | 6        |
| ...      | ...  | ...         | ...   | ...      | 

How do I build this query using the SQL Driver? Or am I completely missing something basic here? I would also be ok with a list of player IDs rather than a team-size count, but I don't think this can be done without querying each widget for players and merging it in with the widget data.

R_J

If you want to stick to the possibilities of the framework, I think the best solution would be to create a view from the subquery and then join this view to your normal query.

To my opinion, if you have a working SQL and building that SQL with the query builder will be a pain, just use the SQL. There seems to be no need to keep SQLs abstract

R_J

Just another thought: maybe it would be easy to update an additional TeamSize column whenever you insert a row into the widget table and so you wouldn't have that extra query whenever you get the information. Fetching the information is surely more often neccessary and more time critical than the insert. So doing extra work while inserting in order to get your results faster might be a good idea anyway

hgtonight

I have the query working on the mysql command line. I wasn't able to slap it in a string and then query it successfully.

As far as adding a column, I would have to add a column to the game table every time a new widget is defined. This doesn't seem like a good idea. No?

R_J

If it runs from command line, it should run from within php/Garden as well. Maybe a quotation issue?
But you could use the query builder in two steps and use the GetSelect function to let Garden build the SQLs for you
$Sql = GDN::SQL() ->Select( 'w.GameID') ->Select( 'w.WidgetID') ->Select( 'w.UserID','count','TeamSize') ->From('widgets w') ->GetSelect();
and
$Sql = GDN::SQL() ->Select() ->From('games g') ->GetSelect();
You'd just have to mix the resukts together, but the query builder will give you the correct starting point. Dont forget to add GDN::SQL()->Reset(); after each query action or the next database call will be screwed up

I have thought of adding the column to widget table. So you could get select g.*,w.* from games g left join widgets w on g.GameID = w.GameID. One of w.* will be TeamSize if you increase it whenever you add a widget row. But to be honest I haven't fully understood your tables.

By the way: just out of curiosity I once did a small research if 'select column1, col2, col3, ...' or a simple 'select *' is better ad specifying each column is faster. You would have to change your SQL though, if there are new columns and that might be a drawback

hbf

@hgtonight said:
I have the query working on the mysql command line. I wasn't able to slap it in a string and then query it successfully.

As far as adding a column, I would have to add a column to the game table every time a new widget is defined. This doesn't seem like a good idea. No?

you can always create a third table that provides the indexed counts without modifying the schema of the original tables. this is generally faster since the indextable will have a stable schema which can be properly indexed by mysql and you can get to your counts very, very fast. updating the counts can be done on the add either directly or you could go with a stored procedure to eliminate the tedium of dealing with the index in every part of the code that might need to touch it.

x00

if you want to denormalise add a count column, however you normally handle basic relational through JOINS not sub queries.

SELECT 
     g.*, COUNT(w.*)  AS 'TeamSize' 
FROM 
     `yarbs_games` AS g 
LEFT JOIN 
     `yarbs_widgets` AS w 
ON 
     w.`WidgetID` = g.`WidgetID`
WHERE 
     w.`GameID` = :id 
GROUP BY 
     g.`Name`

hbf

Joins are your friend. and if you set up your table indexes correctly joins are fast. (if you blow the index definitions, joins are slow on big tables.)

hgtonight

@x00 That query works with one caveat. It falls apart when no widgets are assigned to a particular game yet.

hbf

@hgtonight said:
x00 That query works with one caveat. It falls apart when no widgets are assigned to a particular game yet.

im unclear on the specifics of your query and table specifications. I know you are trying to keep details super-secret-double-probation, but if you can be a little more explicit in your example and what you expect the query to return under multiple conditions then we could help you craft a nifty sql statement.

x00

@hgtonight said:
x00 That query works with one caveat. It falls apart when no widgets are assigned to a particular game yet.

Have you tried a LEFT OUTER JOIN? with g.GameID in the where clause.

I'm slightly confused why you have WidgetID and GameID in both tables.

It would better if you post with example data.

hgtonight

I really appreciate your help and am sorry for the secrecy.

I will write up a proper response with example data soon, but suffice to say a LEFT OUTER JOIN did not work.

x00

if you put FULL OUTER JOIN you should cover the bases. You also have JOIN ON ... OR ...

not the where clause has to pick somethign the is in the game table not just the widget table.

hgtonight

I decided to refactor my tables as I noticed an issue thanks to you guys.

Thanks a lot!

hbf

@hgtonight said:
I decided to refactor my tables as I noticed an issue thanks to you guys.

Thanks a lot!

not to sound smug, but this was the expected outcome as i was reading the thread I bet you can get a beautiful join now.

hbf

in other words: if you cant get meaningful data out of a join of two related tables in a relational database... you're doing it wrong.

hgtonight

I am not very good at designing database schemas and am also pretty bad and designing performant queries. I am learning more as I go along, thanks to you guys!

Thanks for all the help

businessdad

@hgtonight said:
x00 That query works with one caveat. It falls apart when no widgets are assigned to a particular game yet.

If I understood correctly, this should work (even though I don't like to see SELECT * in a query):

SELECT 
  g.*
  ,COUNT(w.*)  AS 'TeamSize' 
FROM 
  `yarbs_games` AS g 
  LEFT JOIN 
  `yarbs_widgets` AS w ON 
    (w.`WidgetID` = g.`WidgetID`) AND
    (w.`GameID` = :id)
GROUP BY 
  g.`Name`