Here is a good, basic example of doing a one-to-many relationship in Reactor ORM.  The Reactor xml syntax for defining this is called <hasMany>.  After, the relationship is defined, Reactor will generate a method with the table name and a iterator() suffix. This method returns an object that allows you to easily iterate over and manipulate sets of related records.  The iterator object has the methods getArray() and getQuery().  Both do exactly the same thing, they just return different data types. HERE is a good source for information on Reactor one-to-many relationships.

<!-- These are the reactor definitions -->
 <object name="lp_property">
      <hasOne name="lp_import_property">
        <relate from="property_id" to="property_id"/>
      </hasOne>
      <!--HERE!, is the many definition -->
      <hasMany name="lp_import_property_contact">
        <relate from="property_id" to="property_id"/>
      </hasMany>
  </object>

The below code will return a query of all the property contacts related to an PropertyRecord:

//use the iterator to get at it, returns query
PropertyRecord.getLp_import_property_contactiterator().getQuery();

This is a good example of a one-to-many relationship using Reactor ORM.  This example is similar to my other post on Reactor relationships, but this one utilizes the <link name>.  The first object "app_news" defines the many relationship, which is the client_navigation table.  Using the <link name> xml we tell it to relate app_news to client_navigation through the newsSectionAssoc name, this is the alias assigned to the app_news_client_navigation_assoc object.  This object uses <hasOne> xml to define the Primary and Foreign keys that link the app_news and client_navigtion tables.  HERE is more documentation on using Reactor relationships.

<object name="app_news">
  <hasMany name="client_navigation">
    <link name="newsSectionAssoc" />
  </hasMany>
</object>

<object name="app_news_client_navigation_assoc" alias="newsSectionAssoc">
   <!--  Assoc -->
   <hasOne name="app_news">
     <relate from="news_id" to="news_id" />
   </hasOne>
   <hasOne name="client_navigation">
     <relate from="client_navigation_guid" to="guid" />
   </hasOne>
</object>

In the CF Reactor query code we use a Reactor left join statement.  This uses all the definitions we defined above in the xml.

   
//this need to be in the reactor statement
query.leftjoin('app_news','newsSectionAssoc', 'newsSectionAssoc','client_navigation')

I know this might be basic for some.  But when i first started using Reactor, seemed like i couldn't find that many good examples of things like this, so i figured i'd throw this up.  Just a example of using ORDER BY and WHERE clause in Reactor.

//good example for sort and where clause
var levelGateway = reactor.createGateway('client_navigation');
var levelQuery   = levelGateway.createQuery();
var NavQuery = "";
      
//join tables w/ reactor
levelQuery.join('client_navigation', 'client_urlpath', 'client_navigation');

levelQuery.getWhere().IsEqual('client_navigation', 'navigation_level_id', 1);
levelQuery.getWhere().IsEqual('client_navigation', 'depth', 0);
levelQuery.getWhere().IsEqual('client_navigation', 'parent_id', 0);
levelQuery.getOrder().setAsc('client_navigation','rank');

//add new data to nav
NavQuery = levelGateway.getByQuery(levelQuery);

arguments.event.setValue("NavQuery", NavQuery);

Example of searching of multiple columns in SQL using LIKE, with a concatenated first and last name.  Nothing real special here, but someone might find this useful.

SELECT first_name
         , last_name
         , email
         , forum_access
         , organization
         , phone
         , customer_id
         , date_created
         , date_deleted
   FROM ecomm_customer
 WHERE date_deleted IS NULL 
    <cfif Len(arguments.searchTerm)>
     AND (COALESCE(first_name,'') + ' ' + COALESCE(last_name, '')) LIKE '%#searchTerm#%' 
      OR email LIKE '%#searchTerm#%'
    </cfif>
    <cfif Len(arguments.dateSearch)>
     AND date_created >= '#arguments.dateSearch#'
    </cfif>   
 ORDER BY date_created DESC

Nice piece of usefull SQL.  This gets a resource category title, for records that have a resource assigned to them.  This could be used in many useful ways.   Not much more to say, just a quick snippet...

SELECT *
  ,(SELECT COUNT(resource_id) 
  FROM app_resource 
WHERE deleted = 0 
    AND active = 1 
    AND category_id = ARC.resource_category_id) AS resource_count
  FROM app_resource_category ARC
WHERE ARC.active = 1
    AND ARC.deleted = 0
    AND EXISTS(SELECT TOP 1 resource_id
                        FROM app_resource
                      WHERE deleted = 0
                          AND active = 1
                          AND category_id = ARC.resource_category_id)