RedBeanPHP
The Power ORM

Extended SQL

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.

@joined

Use @joined to access parent beans directly in SQL:

         $people = R::find( 'person', 
         ' @joined.movement.name = ? ',
         ['romanticism']);

@own

Use @own to access own-lists in SQL:

         $movements = R::find( 'movement',
         ' @own.author.name LIKE ? ', 
         [ 'A%' ]);

@shared

Use @shared to access shared-lists in SQL:

        $people = R::find( 'person', ' @shared.tag.title = ? ', ['writer']);

Chaining

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 = ? ',
         ['Bob']);

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 = ? ',
        ['Albert']);

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 ? ',
         ['a']);

These features can also be used in $books = $author->withCondition( SQL ) - context.


back to main menu

Donate to RedBeanPHP using Monero:
47mmY3AVbRu 7zVVd4bxQnzD
2jR7PQtBJ cF93jWHQ
rP7yRED4qr fqu6G9Q8ZNu7
zqwnB28rz76 w7MaExf
mALVg69yFd 9sUmz
(remove spaces and new lines)

Performance monitor: this page has been generated in 0.017267942428589s. Is the performance lacking? Please drop me an e-mail to notify me!

Partners  
Uurboek.nl PapelDigital

 

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

Privacy Statement