As of RedBeanPHP 5.5 you can use some SQL extensions besides @joined, and you can use those extensions with almost any RedBeanPHP function that accepts an SQL snippet.
Use @joined to access parent beans directly in SQL:
$people = R::find( 'person',
' @joined.movement.name = ? ',
Use @own to access own-lists in SQL:
$movements = R::find( 'movement',
' @own.author.name LIKE ? ',
[ 'A%' ]);
Use @shared to access shared-lists in SQL:
$people = R::find( 'person', ' @shared.tag.title = ? ', ['writer']);
SQL-extensions also work with CTEs (trees) and you can chain them.
$pages = R::children( $website,
' @joined.author.name = ? AND
@own.chart.file = ? AND
@joined.author.shared.role.label = ? ',
['John', 'report.jpg', 'CEO']
In the tree-query above we use multiple SQL-extensions together and we also chain them, @joined.author.shared.role.label will access the author parent beans and then check the label associated with its shared role. You can make it as complex as you wish. Under the hood, RedBeanPHP translates these @-lists to JOINS.
Aliasing in SQL-extensions
Use aliases in SQL-extensions as follows:
$books = R::find('book',
' @joined.person[as:author].firstname = ? ',
This will find any book written by Bob, checking the name using the author-property of the bean instead of the person property. You can also use aliases for own-lists:
$authors = R::find( 'person',
' @own.book[alias:author].title LIKE ? ',
[ '%Study%' ]);
Separate multiple aliases with a slash:
$books = R::find( 'book',
' @joined.person[as:author/coauthor].firstname = ?',
[ 'Bob' ] );
Note that the word in front of the colon (as:) is only there for yourself as a reminder, RedBeanPHP figures out how to use the alias depending on the type of list you access. Of course you can chain aliased items as well:
$texts = R::find('text',
' @joined.book[as:magazine/source/book].joined.person[as:author/coauthor].firstname = ? ',
Vias in Extensions
To treat an intermediate bean type or two complimentary N-1 relations as as a N-M relation in SQL, you can use the via-operator as well:
$companies = R::find('company',
' @joined.work.shared.employee[via:participant].name LIKE ? ',
These features can also be used in $books = $author->withCondition( SQL ) - context.
back to main menu
Donate to RedBeanPHP using Monero:
(remove spaces and new lines)
Performance monitor: this page has been generated in 0.018627166748047s. Is the performance lacking? Please drop me an e-mail to notify me!
RedBeanPHP Easy ORM for PHP © 2023 Gabor de Mooij
() and the RedBeanPHP community
(credits) - Licensed New BSD/GPLv2 -
RedBeanPHP, the power ORM for PHP since 2009.