Forums

DatabaseItemList :: Scope

Permalink
Contemplating custom architecture:

EDITED:

*_list models which extend the DatabaseItemList can be made to search various tables. The use of aliases threw me. Note the setBaseQuery within the *List class.

Can someone explain how the $tbl variable (ex. "ug_4") works in the JOIN sql statement? I assume it's getting parsed into "ug" and "4" but I'm not sure where. 

public function filterByGroup($groupName='', $inGroup = true){ 
      $group=Group::getByName($groupName); 
      $tbl='ug_'.$group->getGroupID();
      $this->addToQuery("left join UserGroups $tbl on {$tbl}.uID = u.uID ");   
      if ($inGroup) {
         $this->filter(false, "{$tbl}.gID=".intval($group->getGroupID()) );
      } else {
         $this->filter(false, "{$tbl}.gID is null");
      }
   }


Rick
Ricalsin
 
Ricalsin replied on at Permalink Reply
Ricalsin
Found it in the Developer Documents:http://www.concrete5.org/documentation/developers/pages/searching-a...

Quoted text: "Passes a fliter directly to the "WHERE" clause. The value of $column must be a valid database column that's referenced in the PageList query."

To the point: "Setting the value of $column to false will allow you to pass complex SQL into the $value field ex:
$pl->filter(false, '(ak_age = 10 OR ak_age IN (13,17,25) OR ak_age > 23)');
where 'age' would be the handle of a numeric page attribute"
Ricalsin replied on at Permalink Reply
Ricalsin
When I debug a user category query I get:
(mysql): SELECT DISTINCT u.uID, u.uName FROM Users u left join
UserSearchIndexAttributes on (UserSearchIndexAttributes.uID = u.uID)
where 1=1 order by uDateAdded desc limit 0,10


I understand the setBaseQuery() and executeBase() building of the SQL, but for some reason the role of the *searchIndexedAttributes table has me mentally tweaked.

I guess its merely a quicker way to search the categories attributes rather than having to grab the controllers for each search query attribute type and access each table separately. Is that it?
Ricalsin replied on at Permalink Reply
Ricalsin
For anyone interested:

A C5 attribute category's *searchIndexedAttributes table makes finding an attribute category's attribute value within the C5 filtration system. The table is constructed by the C5 core and updated by the reindex() function when an attribute is either saved, updated or deleted. The table itself is joined in an attribute Category's *list class search query in order to quickly find the category's attribute values that are being searched.

If you intend to tie information together across multiple dbs it is beneficial to learn the C5 approach.