352 * the later will retrieve all versions, state and tags (cartesian product!), |
352 * the later will retrieve all versions, state and tags (cartesian product!), |
353 compute join and then exclude each row which are in the matching state of or |
353 compute join and then exclude each row which are in the matching state of or |
354 tagged by the expected tag. This implies that : you won't get any result if the |
354 tagged by the expected tag. This implies that : you won't get any result if the |
355 in_state or tag |
355 in_state or tag |
356 |
356 |
357 |
357 Another common case where you may want to use ``EXISTS`` is when you |
358 You can also use the question mark (`?`) to mark optional relations which allow |
358 find yourself using ``DISTINCT`` at the beginning of your query to |
|
359 remove duplicate results. The typical case is when you have a |
|
360 multivalued relation such as Version version_of Project and you want |
|
361 to retrieve projects which have a version: |
|
362 |
|
363 .. sourcecode:: sql |
|
364 |
|
365 Any P WHERE V version_of P |
|
366 |
|
367 will return each project number of versions times. So you may be |
|
368 tempted to use: |
|
369 |
|
370 .. sourcecode:: sql |
|
371 |
|
372 DISTINCT ANY P WHERE V version_of P |
|
373 |
|
374 This will work, but is not efficient, as it will use the ``SELECT |
|
375 DISTINCT`` SQL predicate, which needs to retrieve all projects, then |
|
376 sort them and discard duplicates, which can have a very high cost for |
|
377 large result sets. So the best way to write this is: |
|
378 |
|
379 .. sourcecode:: sql |
|
380 |
|
381 ANY P WHERE EXISTS V version_of P |
|
382 |
|
383 |
|
384 You can also use the question mark (`?`) to mark optional relations. This allows |
359 you to select entities related **or not** to another. It is a similar concept |
385 you to select entities related **or not** to another. It is a similar concept |
360 that the `Left outer join`_: |
386 to `Left outer join`_: |
361 |
387 |
362 the result of a left outer join (or simply left join) for table A and B |
388 the result of a left outer join (or simply left join) for table A and B |
363 always contains all records of the "left" table (A), even if the |
389 always contains all records of the "left" table (A), even if the |
364 join-condition does not find any matching record in the "right" table (B). |
390 join-condition does not find any matching record in the "right" table (B). |
365 |
391 |
366 You must use the `?` behind a variable to specify that the relation toward it |
392 You must use the `?` behind a variable to specify that the relation to |
367 is optional. For instance: |
393 that variable is optional. For instance: |
368 |
394 |
369 - Bugs of a project attached or not to a version |
395 - Bugs of a project attached or not to a version |
370 |
396 |
371 .. sourcecode:: sql |
397 .. sourcecode:: sql |
372 |
398 |
373 Any X, V WHERE X concerns P, P eid 42, X corrected_in V? |
399 Any X, V WHERE X concerns P, P eid 42, X corrected_in V? |
374 |
400 |
375 You will get a result set containing all the project's tickets, with either the |
401 You will get a result set containing all the project's tickets, with either the |
376 version in which it's corrected or None for tickets not related to a version. |
402 version in which it's fixed or None for tickets not related to a version. |
377 |
403 |
378 |
404 |
379 - All cards and the project they document if any |
405 - All cards and the project they document if any |
380 |
406 |
381 .. sourcecode:: sql |
407 .. sourcecode:: sql |