current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

Intro

I'm following this guide here and I'm stuck on the very last part: http://symfony.com/doc/2.0/cookbook/security/entity_provider.html

I'm trying to query from the account table and join the group table as well. It's a many to many relationship with an intermediate table account_group.

account Table Fields: id, firstName, and email

account_group Table Fields: account_id and group_id

group Table Fields: id, name, and role

Problem

Here is my DQL:

        SELECT a.firstName, g.role
        FROM WikiRosterMainBundle:Account a
        LEFT JOIN a.groups g
        WHERE a.email = :email

I'm getting an internal server error:

An exception occurred while executing 'SELECT a0_.id AS id0, a0_.firstName AS firstName1, a0_.email AS email3, g1_.role AS role18 FROM account a0_ LEFT JOIN account_group a2_ ON a0_.id = a2_.account_id LEFT JOIN group g1_ ON g1_.id = a2_.group_id WHERE a0_.email = ?' with params {"1":"[email protected]"}:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group g1_ ON g1_.id = a2_.group_id WHERE a0_.email = '[email protected]'' at line 1

What I've tried:

+Adding a primary key to the account_group table (didn't work)

+Writing my own left join mysql query and inputing it in phpmyadmin (works with a single left join, but fails when I attempt to join 3 tables)

+Flipping the table I'm joining / selecting from

Research I've done

It seems like left join changed in mysql 5. I've looked at documentation and other stack overflow questions:

http://dev.mysql.com/doc/refman/5.0/en/join.html

MySQL LEFT JOIN after 5.0.12 changes - How to rewrite query

Thanks for any help!

share|improve this question

2 Answers 2

up vote 1 down vote accepted

The basic problem is that group is a reserved work in sql (aka GROUP BY). It's possible to work around the problem with back ticks but you would be better off to just rename the table to something else such as groupx. You can keep your entity as group, just need to change the table name.

share|improve this answer
    
Thank you so much! It makes perfect sense. –  Jason Lin Mar 28 '13 at 21:54
up vote 1 down vote

As mentioned above, the biggest problem is naming the table Group. You'll need to use backticks every time you reference it. I prefer to avoid them but it's your call. Here's how to do the join:

SELECT a.FirstName, g.Role
FROM Account a
LEFT JOIN account_group ag ON a.id = ag.account_id
LEFT JOIN `group` g ON ag.group_id = g.id
share|improve this answer
1  
Jason is using DQL so that doesn't really help. The syntax will a bit different. –  Cerad Mar 28 '13 at 21:53
    
Thanks @Cerad for the heads-up! I had a major brain fart here and thought the "DQL" was a typo. Even the query looking strange-ish wasn't enough to clue me in. Embarrassing :) –  Ed Gibbs Mar 28 '13 at 21:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.