Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

'where not in' query with doctrine query builder

Im trying to reproduce this query:

SELECT * FROM `request_lines` where request_id not in( select requestLine_id from `asset_request_lines` where asset_id = 1  ) 

in doctrine query builder, I am stuck on the where request_id not in(select

I currently have:

$linked = $em->createQueryBuilder()         ->select('rl')         ->from('MineMyBundle:MineRequestLine', 'rl')         ->where()         ->getQuery()         ->getResult(); 
like image 803
Andrew Atkinson Avatar asked Dec 19 '12 16:12

Andrew Atkinson


People also ask

Is Query builder an ORM?

The ORM's query builder provides a simple to use fluent interface for creating and running queries.

How does query builder work?

Query Builder is designed to enhance productivity and simplify SQL query building tasks. Query Builder provides a graphical user interface for creating SQL queries. You can drag-and-drop multiple tables, views and their columns onto a visual designer to generate SQL statements.

What is Doctrine query?

Doctrine Query Language (DQL) is an Object Query Language created for helping users in complex object retrieval. You should always consider using DQL (or raw SQL) when retrieving relational data efficiently (eg. when fetching users and their phonenumbers).

What is Query builder in C#?

SQL query builder, written in c#, helps you build complex queries easily, supports SqlServer, MySql, PostgreSql, Oracle, Sqlite and Firebird. sqlkata.com.


1 Answers

You need to use query builder expressions, and this means you need access to the query builder object. Also, the code is easier to write if you generate the subselect list ahead of time:

$qb = $em->createQueryBuilder();  $nots = $qb->select('arl')           ->from('$MineMyBundle:MineAssetRequestLine', 'arl')           ->where($qb->expr()->eq('arl.asset_id',1))           ->getQuery()           ->getResult();  $linked = $qb->select('rl')              ->from('MineMyBundle:MineRequestLine', 'rl')              ->where($qb->expr()->notIn('rl.request_id', $nots))              ->getQuery()              ->getResult(); 
like image 175
Lighthart Avatar answered Oct 22 '22 04:10

Lighthart