symfony doctrine three tables one query to Pager (Pagination)?

I have three tables:

Table 1: Images ------------------------ ID | user_id | title ------------------------ 1 | 0 |Image 1 2 | 1 |Image 2 Table 2: Images_Upload (image_id = Table Images ID) ------------------------ ID | image_id | file ------------------------ 1 | 1 |image.png Table 3: User ------------------------ ID | username ------------------------ 1 | user1

On the Website, the users can Upload an Image. If the User is Logged In, and Uploads an Image, then the user_id on Table Image is the users id from Table User. If the User is not Logged In, so the user_id is 0 on Table Image.

I want now list the title, show the image and show the username. (If the user_id = 0, then i will show "noUsername")

$q = Doctrine_Query::create() ->select('*,e.title, n.file AS file, IF(e.user_id = 0,"noUsername",k.username) AS username') ->from('Images e, ImagesUploads n, User k') ->andWhere('e.id = n.id') ->andWhere('e.user_id = k.id OR e.user_id not k.id'); $this->pager = new sfDoctrinePager( 'Images', sfConfig::get('app_max_image') ); $this->pager->setQuery($q); $this->pager->setPage($request->getParameter('page', 1)); $this->pager->init();

I can't get the usernames.

-------------Problems Reply------------

------------------------
ID | image_id | file
------------------------
1 | 1 |image1.png
2 | 2 |image2.png

Yeah. Sorry. I'm new here. I answered.

Category:symfony1 Views:0 Time:2011-01-29

Related post

  • Symfony & Doctrine getting a joined query to work 2012-03-05

    I've got an SQL query that returns all the rows in one table (country) which have a related entry in another table (ducks) but I'm struggling to turn this into DQL. This is a standard one-many relationship as each country can have multiple ducks, I b

  • Symfony/Doctrine class table inheritance and foreign key as primary key 2014-06-17

    I am currently designing a web application with Symfony 2.5 (and Doctrine 2.4.2) that has to be flexible to easily plug in new modules/bundles. So I have an entity (let say A) that has two one-to-one associations with abstract classes (B and C). The

  • Symfony doctrine::build task cannot create table named order 2011-04-25

    I have a database with a table named order. When i run php symfony doctrine:build --all, i got the folowing error: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your

  • symfony doctrine pagination brings more data than set per page 2011-09-07

    I am setting 1 row per page in symfony pager but its showing more data than 1. It is because my query is left joining the sfGuardUser table with other tables and it is causing duplication of 'sfGuardUser' row in some cases when there are multiple ent

  • How do i detach a behavior in Symfony/Doctrine? 2009-09-24

    I have doctrine's softdelete behavior attached to all of my models. Is there a way I can hard delete a particular record? In cakephp I remember detaching the behavior... deleting the record and then re attaching the behavior. Is there something simil

  • symfony doctrine build-sql error 2010-04-10

    I have some big problems with symfony and doctrine at the beginning of a new project. I have created database diagram with mysql workbench, inserted the sql into phpmyadmin and then I've tried symfony doctrine:build-schema to generate the YAML schema

  • Altering a database in symfony (doctrine) 2010-09-16

    I'm a beginner with symfony (1.4 + Doctrine), but there is a point which scares me. It seems that whenever one wants to change a model, the only way is to change the schema for the database (config/doctrine/schema.yml) and then call symphony doctrine

  • Symfony+Doctrine: Correct YAML syntax for default value of boolean field 2010-11-21

    I am working on the schema for my Symfony app, and I need to set the default value of two boolean fields to false. However, with all the ways I've tried to do it, when the sql gets generated, it comes out with the default keyword, but no default valu

  • Symfony doctrine i18n behavior data-load failure: Integrity constraint violation 2011-01-14

    I want tho have I18N categories table. I have followed the jobeet example. Schema and fixtures data below. ./symfony doctrine:build --db --all-classes --and-migrate ./symfony doctrine:data-load data/fixtures/category.yml When I run these commands no

  • Symfony doctrine migrations problem 2011-08-01

    I use symfony 1.4.11 , I have a project... I have schema.yml , and I have migrations with tables which are not in the schema. For example I have in my db "pages" table, and it not described in schema. When I get project in first time I make: build --

  • Symfony Doctrine sfDoctrinePager and LIMIT 2012-03-11

    I have a table with 500 users. I need to show the last 100 registred users on my homepage, paginated. So I am using symfony and the pagination with sfDoctrinePager. Action: $query = UserTable::getLast100UsersQuery(); $pager = new sfDoctrinePager('Use

  • unit testing with symfony + doctrine + PHPUnit 2009-09-10

    I'm working with Symfony + Doctrine + PHPUnit, with NetBeans IDE. Here' my current approach to unit testing. setUp() function loads the test fixtures from .yml files tearDown() function delete all data from models. this is done by looping through an

  • In symfony/doctrine's schema.yml, where should I put onDelete: CASCADE for a many-to-many relationship? 2009-09-28

    I have a many-to-many relationship defined in my Symfony (using doctrine) project between Orders and Upgrades (an Order can be associated with zero or more Upgrades, and an Upgrade can apply to zero or more Orders). # schema.yml Order: columns: order

  • how to resolve symfony doctrine:build-schema error (Unknown relation alias table_name) 2009-10-26

    how to resolve this symfony error : C:\inetpub\wwwroot\project\trunk\preprod\signup>php symfony doctrine:build-schema --trace >> doctrine generating yaml schema from database [sfException] Unknown relation alias table_name Exception trace: a

  • Optimize MYSQL (3) Table Join Query 2010-01-31

    Running an EXPLAIN on some of my query tests have resulted in slow ALL joins even with indexes. How do I make a MYSQL query with the following information more efficient? Tables counter: id (pk), timestamp, user_id (fk) user: id (PK), username, websi

  • symfony doctrine:insert-sql - "Couldn't locate driver named mysql" error 2010-04-28

    hey, I am a newbie in symfony. I am following this joobet tutorial on symfony-project.com, I am on there day 3 http://www.symfony-project.org/jobeet/1_4/Doctrine/en/03 Whenever I type php symfony doctrine:insert-sql, I get the following error: doctri

  • symfony + doctrine + inheritance, how to make them work? 2010-05-05

    I am beginning to work with Symfony, I've found some documentation about inheritance. But also found this discouraging article, which make me doubt if Doctrine handles inheritance any good at all... Has anyone find a smart solution for inheritance in

  • How to make 'symfony doctrine:build-sql' task generate 'DROP' statments? 2010-09-02

    Is there a way to make symfony doctrine:build-sql task generate DROP statments before CREATE TABLE ones as it symfony propel:build-sql does ? ? --------------Solutions------------- There is no such possibility, but you can run the doctrine:drop-db ta

  • Symfony doctrine:generate-module custom theme: cannot find templates for i18n embedded field 2010-09-05

    I generated a custom theme by copying plugins/sfDoctrinePlugin/data/generator/sfDoctrineModule/default" into plugins/sfDoctrinePlugin/data/generator/sfDoctrineModule/mytheme1" I changed the templates to match my needs (no tables, custom errors displa

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.244 (s). 11 q(s)