Union select

garymardell · August 2009

Hey,
Whats the support like for Unions?
I couldn't see anything regarding it.
If anyone could change this into a nicer query then please do.
I have another alternate method but this would be nicer if it i could convert it to the sql class.


            $Sql = "(SELECT f.FriendUserID as id, u.Name ";
            $Sql .= "FROM GDN_Friend as f ";
            $Sql .= "LEFT JOIN GDN_User as u ON f.FriendUserID = u.UserID ";
            $Sql .= "WHERE f.FriendUserID = ".$UserID.")";
            $Sql .= " UNION ";
            $Sql .= " (SELECT f.UserID as id, u.Name ";
            $Sql .= "FROM GDN_Friend as f ";
            $Sql .= "LEFT JOIN GDN_User as u ON f.UserID = u.UserID ";
            $Sql .= "WHERE f.UserID = ".$UserID.")";
            $Sql .= " ORDER BY RAND() LIMIT ".$Limit."";


            return $this->SQL->Query($Sql);

Thanks for your help. As you can see its not pretty but necessary for my application.
Writing queries at 3am is always good for you.

Todd · August 2009

3am? You're a machine. We don't have unions in our sql object right now. I'm think you could either:

a) Run both queries separately and merge the result arrays.

b) Try a where $UserID in (f.UserID, f.FriendUserID). In this case I think you need to do something like this: $SQL->WhereIn('@'.$UserID, array('@f.UserID', '@f.FriendUserID'));

c) Have some redundant data where there are always two rows for each friend relationship. This option might be the best as far as table indexing goes.

garymardell · August 2009

With option a) the problem is i need to select say 7 friends so with two queries i would have to select every friend i guess and pick one at random from the resultset.

It would be nice to do something like:


$this->SQL->QueryGroup("(")
    ->Select(....)
    ->From(....)
    ->Join(....)
    ->Where(....)
    ->EndQueryGroup(")")
    ->Union
->QueryGroup("(")
    ->Select(....)
    ->From(....)
    ->Join(....)
    ->Where(....)
    ->EndQueryGroup(")")
    ->OrderBy(....)
    ->Limit(....)
    ->Get();

This way it could be the first query group and wrap it in the delimiters ( and )
Either append the union or store it for later, create the second query group
and then build the whole query from that. And i think the syntax looks quite nice.

I have got a solution now that will select both parts and requires some additional logic in the module to prevent repeating data by checking if the userid of the profile is the same as one column or the other. So i may revert back to that for now saving this query for later. The query means i don't have to add more logic.

c) I can do it without repeating data, i thought that would be the only way at first, however if there are 10 people with 10 friends each that would mean 200 rows in the table.

It could be that there are 100 people with on average 15 friends each which would mean 3000 rows when it could be just 1500. I can imagine with a forum that is popular it would soon get out of hand.

Option b) is about the same as writing a where and a orwhere isnt it? I would still need the addition logic in the view to know which column is the profile id and which is the id of the friend.

garymardell · August 2009

Well i have made a fairly hackish version as well partially using the SQL driver.


$First = $this->SQL
            ->Select('f.FriendUserID', '', 'id')
            ->Select('u.Name')
            ->From('Friend f')
            ->Join('User u', 'f.FriendUserID = u.UserID')
            ->Where('f.UserID', $UserID)
            ->GetSelect();
            
        // clear query 
        $this->SQL->Reset();
        
        $Last = $this->SQL
            ->Select('f.UserID', '', 'id')
            ->Select('u.Name')
            ->From('Friend f')
            ->Join('User u', 'f.UserID = u.UserID')
            ->Where('f.FriendUserID', $UserID)
            ->GetSelect();
            
        $Sql = "(".$First.") UNION (".$Last.") ";
        $Sql .= " ORDER BY RAND() LIMIT ".$Limit."";
        
        $Sql = str_replace(":fUserID", $UserID, $Sql);
        $Sql = str_replace(":fFriendUserID", $UserID, $Sql);
        return $this->SQL->Query($Sql);

You can see its not the most ideal solution, my solution above would be perfect and i may try working on it soon to get Unions working.

Todd · August 2009

I would say repeating data is definitely the best way to go.

A friends feature is a great idea for the forum and I can picture it being used for a lot of other queries. If it has to go through a difficult union select then it will be really difficult when other parts of the forum have to use it. Twice as much data is not as bad and the friend table will be tiny compared to the comment table.

garymardell · August 2009

I have reverted back to using an orwhere and in the view displaying one thing if it was in the userID column or another if it was in the FriendUserID column. This seems the easiest way other than a union. Well that's the whole idea of the model to make it easier for other parts of the forum to use it. I can always change the schema later, just want to get it up and running, then i can work on optimizing it later. Does garden have a profiler of any kind, like codeigniter's profiler to see how long certain scripts took and the overall load times. My next plugin may be to include http://particletree.com/features/php-quick-profiler/ to garden hopefully. And i may work on creating the union feature into garden to commit to the core. If there are no plans to add it that is.

Todd · August 2009

Cool, can't wait to see your plugin.

We have a debugger plugin that displays all of the queries execute on the page, but it doesn't have as much info as PHP quick profiler. Sounds like an awesome plugin.

garymardell · August 2009

Well, i found out someone else is writing a friends application in the other post, so didn't know whether to carry on anyways. Maybe at somepoint if we both release one we could team up to make a better friends plugin i guess. Oh and note it won't be the best plugin in the world being my first, but should get me more familiar with the system.

I think that would be good to include a profiler, will make testing different queries easier for their speed.

Union select

Comments