Converting SQL query into Doctrine, Error

Question

I am trying to convert a SQL query into a Doctrine QueryBuilder query. The SQL query works as I want it to within my SQL console (Workbench). But When I converted ,my SQl query into Doctrine 'Query' i get an error i cannot get my head around.....

Pure SQL query:

    select 
CONCAT('news/', feeds.slug, '/', articles.slug) as 'URL'

from article_feeds
left join articles on article_feeds.article_id=articles.id
left join feeds on article_feeds.feed_id=feeds.id

DOCTRINE Query:

$stmt = $this->db->createQueryBuilder()
         ->select('faf.article_id', 'faf.feeds_id')
         ->addSelect('f.slug AS feedSlug')
         ->addSelect('a.slug AS articleSlug')
         ->addSelect("Group_CONCAT(DISTINCT CONCAT(feedSlug, '/' , articleSlug) SEPARATOR ',' ) AS url")
         ->from('article_feeds', 'faf')
         ->leftJoin('a', 'article_feeds', 'faf', 'af.article_id = a.id')
         ->leftJoin('f', 'article_feeds', 'faf', 'af.article_id = f.id');


 $this->urls = $stmt->execute()->fetch(\PDO::FETCH_OBJ);

Error:

{
code: 0,
message: "The given alias 'articles' is not part of any FROM or JOIN clause table. The currently registered aliases are: faf."
}

From What I understand there is a problem with my leftJoin's but why and what is it, how do I fix it.....?

Cedric Chabert · Accepted Answer · 2014-11-27 11:20:40Z

1

->leftJoin('faf.articles', 'your_articles_alias')

answered Nov 27, 2014 at 11:20

Cedric Chabert

421 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tomasz Madeyski · Accepted Answer · 2014-11-27 11:44:11Z

0

You're using leftJoin method wrong.

 /*
 * @param string $join The relationship to join
 * @param string $alias The alias of the join
 * @param string $conditionType The condition type constant. Either ON or WITH.
 * @param string $condition The condition for the join
 * @param string $indexBy The index for the join
 * @return QueryBuilder This QueryBuilder instance.
 */
public function leftJoin($join, $alias, $conditionType = null, $condition = null, $indexBy = null)

So your query should look like:

$stmt = $this->db->createQueryBuilder()
     ->select('article_feeds.article_id', 'article_feeds.feeds_id')
     ->addSelect('feeds.slug AS feedSlug')
     ->addSelect('articles.slug AS articleSlug')
     ->addSelect("Group_CONCAT(DISTINCT CONCAT(feedSlug, '/' , articleSlug) SEPARATOR ',' ) AS url")
     ->from('article_feeds', 'article_feeds')
     ->leftJoin('articles', 'articles','ON', 'article_feeds.article_id = articles.id')
     ->leftJoin('feeds', 'article_feeds', 'ON', 'article_feeds.article_id = article_feeds.id');

I'm not sure if relations are good here, but the thing is that relations should be set in your entity class - then you don't need to set them in leftJoin method and you can use only two first parameters

edited Nov 27, 2014 at 11:44

answered Nov 27, 2014 at 11:23

Tomasz Madeyski

10.9k3 gold badges54 silver badges66 bronze badges

3 Comments

Tomazi Over a year ago

Hey thx for your replay, Sorry if this might be a lame question but what is the entity class...?

Tomazi Over a year ago

Hey I am actually quering DB directly, I do not have Entity elements/Map in my project at all.

Tomazi Over a year ago

Thank you for trying to help but the query you have provided still does not work: Error: `` { code: 0, message: "The given alias 'faf.articles' is not part of any FROM or JOIN clause table. The currently registered aliases are: faf." }

Collectives™ on Stack Overflow

Converting SQL query into Doctrine, Error

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related