Doctrine query builder using inner join with conditions

by

I’m going to answer my own question.

  1. innerJoin should use the keyword “WITH” instead of “ON” (Doctrine’s documentation [13.2.6. Helper methods] is inaccurate; [13.2.5. The Expr class] is correct)
  2. no need to link foreign keys in join condition as they’re already specified in the entity mapping.

Therefore, the following works for me

$qb->select('c')
    ->innerJoin('c.phones', 'p', 'WITH', 'p.phone = :phone')
    ->where('c.username = :username')
    ->setParameter('phone', $phone)
    ->setParameter('username', $username);

or

$qb->select('c')
    ->innerJoin('c.phones', 'p', Join::WITH, $qb->expr()->eq('p.phone', ':phone'))
    ->where('c.username = :username')
    ->setParameter('phone', $phone)
    ->setParameter('username', $username);;

More Related Contents:

  1. Doing a WHERE .. IN subquery in Doctrine 2
  2. How do I create a parameterized SQL query? Why Should I?
  3. Auto Increment after delete in MySQL
  4. Left Join With Where Clause
  5. Update table values from another table with the same user name
  6. What is the string concatenation operator in Oracle?
  7. Using an Alias in a WHERE clause
  8. How can I introduce multiple conditions in LIKE operator?
  9. How do I delete from multiple tables using INNER JOIN in SQL server
  10. Does Foreign Key improve query performance?
  11. SQL query for finding records where count > 1
  12. Using sql function generate_series() in redshift
  13. SQL Server convert string to datetime
  14. Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)
  15. Return number of rows affected by UPDATE statements
  16. How does this CASE expression reach the ELSE clause?
  17. SQL speed up performance of insert?
  18. SQL Server: Cannot insert an explicit value into a timestamp column
  19. Error: Column does not exist
  20. TransactSQL to run another TransactSQL script
  21. How to Concatenate Numbers and Strings to Format Numbers in T-SQL?
  22. SQL server identity column values start at 0 instead of 1
  23. SQL method to replace repeating blanks with single blanks
  24. How can I get multiple counts with one SQL query?
  25. Rails 4 LIKE query – ActiveRecord adds quotes
  26. How do I view grants on Redshift
  27. SQL – How do I get only the numbers after the decimal?
  28. How to retrieve records for last 30 minutes in MS SQL?
  29. Connecting to Oracle Database using Sql Server Integration Services
  30. Executing SQL query on multiple databases

Leave a Comment