RedBeanPHP
The Power ORM

SQN

SQN - Short Query Notation is a plug-in that allows you to write convention based SQL queries using a short notation. SQL is a very flexible and powerful language but because SQL does not rely on conventions you have to specify everything. The SQN-library requires some naming conventions. Usage:

    R::sqn('shop<product<price');

The code above will left join shop, product and price. This will result in the following SQL code:

SELECT `shop`.* ,
`product`.* ,
`price`.*
FROM `shop`
LEFT JOIN `product` ON `product`.shop_id = `shop`.id
LEFT JOIN `price` ON `price`.product_id = `product`.id

To doubly left join beans book and tag using book_tag as a linking bean:

    R::sqn('book<<tag');
SELECT `book`.* ,
`tag`.* ,
`book_tag`.*
FROM `book`
LEFT JOIN `book_tag` ON `book_tag`.book_id = `book`.id
LEFT JOIN `tag` ON `book_tag`.tag_id = `tag`.id

SQN assumes id fields follow the following conventions:

  • Primary key: id
  • Foreign key: {table}_id
  • Link table: {table1}_{table2} (alphabetical order)

SQN can also generate additional aliases for you:

    R::sqn( ..., 'area/x,y;place/x,y' )

For instance:

    R::sqn('zipcode<area<place','area/x,y;place/x,y');
SELECT `zipcode`.* ,
`area`.* ,
`place`.* ,
`area`.`x` AS `area_x` ,
`area`.`y` AS `area_y` ,
`place`.`x` AS `place_x` ,
`place`.`y` AS `place_y`
FROM `zipcode`
LEFT JOIN `area` ON
`area`.zipcode_id = `zipcode`.id
LEFT JOIN `place` ON
`place`.area_id = `area`.id

The syntax for the main query (from and joins) is extremely simple. Just add all the tables you wish to join. The first table will be the from-table. Others are joined. The join-symbol tells SQN how to join the table:

SymbolMeaning
<LEFT JOIN
<<DOUBLE LEFT JOIN
>RIGHT JOIN
>>DOUBLE RIGHT JOIN
|INNER JOIN
||DOUBLE INNER JOIN



For most queries SQN can reduce the verbosity of standard SQL.


back to main menu

RedBeanPHP Easy ORM for PHP © 2019 () and the RedBeanPHP community () - Licensed New BSD/GPLv2 - RedBeanPHP Archives
RedBeanPHP, the power ORM for PHP since 2009.

RedBeanPHP is part of the GWorx Application Server Platform. GWorx Application Server is a product of Gabor Software © copyright 2019. Learn more about GWorx.