Welcome Guest, Not a member yet? Register   Sign In
Stuck on complex query using querybuilder
#1

I'm trying to use query builder for this, but if I have to go straight sql I'll live with it!
That's why I'm posting here in CI forums and not just in a mysql forum.

I am trying to figure out the best way to solve this in a single query to avoid making additional data calls.

I have 5 tables involved:
event group (EG) - for all groups of events (egid, title)
events (EV) - individual events, connected to EG using EG ID (evid, title, date)
members (MEM) - individual members that will belong to EG's (many to many using MG) (memid, name etc)
memgroup (MG) - connects MEM to EG using MEM ID and EG ID (mgid, memid, egid)
results (RS) - results from the events, connected to MEM by MEM ID (memid, evid, points etc)

What I am stuck on is I have a form to fill in results, that is based on the single EV ID

I can grab all the MEMs that belong to the EG that the EV belongs to using a MEM left join to MG, no problem.

what I cant figure out is how to only show the MEMs that don't have RS's for that EV so the form can have a list of of just non-entered MEM results for this event to make it easier for non-techies to fill out and not screw it up with duplicated entries etc. or scrolling threw 50 names for every entry.

Basically select members that are are in group that this event belongs to that have not had results posted yet, by passing just the event ID

Or maybe there is a competely different way to approach this, I can't think of one haha!
Reply
#2

Interesting question! I am NOT an expert in any way and can not do it in one query. I can do it in 3 and a bit of php. (This is like query golf).

Query 1: Get MEM id's from RS
Query 2: Get MEM id's from EG

new array NOT INCLUDED ID's = Use array_diff to get MEM id's in query 2 not in query 1

query 3: Select MEM ids where in(NOT INCLUDED ID's)

Ta da!!!

OR I just got it down to 2 queries - sorry, just remembered about where_not_in

Query 1: Get MEM id's from RS
Query 2: Get MEM's from EG where_not_in(Query 1)

:-)

I am sure that does not help, as you already said you can do it but wanted it in one query but could not resist answering. However, I would love to see a single query too. How would you do it with SQL? (without just nesting one query in another).

Paul
Reply
#3

That I haven't figured out exactly either, I also tried multiple joins but I can seem to get that to work.

Its not a big deal at this point, but I'm thinking if this site grows it could be a sore spot with taxing the server.

thanks for the attempt though! Smile
Reply
#4

Code:
    $this->db->select('*');
   $this->db->from('memgroups');
   $this->db->where('memgroups.groupId', $event['groupId']);
   $this->db->join('members', 'memgroups.memberId=members.memberId', 'left');
   $this->db->join('results', 'members.memberId != results.memberId AND eventID = '.$event['eventID'], 'inner');
   $members = $this->db->get()->result_array();
This works, but only if at least one entry exists in results, otherwise it returns null. 

I tried a couple variations of join types but can't get it to work... so dang close! haha!
Reply
#5

The problem here is that you're trying to select something that isn't in the database. In order to do this, you're probably going to need an inner query to select the list of members who have results, then use an outer query to give you the list of members not in the result set of the inner query. It's much easier to build a query like that in SQL and either create a stored procedure for it or use $this->db->query() rather than trying to use query builder.
Reply




Theme © iAndrew 2016 - Forum software by © MyBB