In response to the getRows() poor design we came up with a better way to build complex queries using Eden's search object. An overview example can be found in Figure 10.Figure 10. SQLite Search
$database ->search('user') ->setColumns('*') ->innerJoinOn('group', 'group_owner=user_id') ->leftJoinUsing('friends', 'user_id') ->filterByUserName('Chris') ->addFilter("user_last LIKE '%s%%'", 'Brown') ->sortByUserId('ASC') ->addSort('user_last', 'DESC') ->setRange(25) ->setStart(75) ->getRows();
In the figure above there's a few methods being powered with magic, but we'll just start going down the line. First off, to instantiate the search object you simply need to callsearch() passing the name of the table as the argument. Secondly we call setColumns(). This call is optional, but if used, can either accept an array of columns or an argument separated list of columns, ie. setColumns('user_id', 'user_name'). Next, innerJoinOn() is the new way we accept joins. There are 8 methods dedicated to different kinds of joins.
No matter what methods you choose from above there are 2 arguments you need to add. The first argument is the name of the table you would like to join and the second one is the how they relate to each other.
The first magic powered method is called filterByUserName(). There is no actual method called filterByUserName() in the SQLite class. Instead when this function is called it will parse out the name of the method and recognize that UserName is the name of a column and convert that into addFilter('user_name=%s', 'Chris') as in Figure 10.
addFilter() generally accepts 2 arguments. The first argument is the filter clause. If you notice in our filter example in Figure 6 we use %s to delimit a binded value. You can have as many binded values per filter as you like. The following arguments need to include the binded values in order of when they occur in the filter clause.
The second magic powered mehod is called sortByUserId('ASC').There is no actual method called sortByUserId('ASC') in the SQLite class. Instead when this function is called it will parse out the name of the method and recognize that UserId is the name of a column and convert that into addSort('user_id', 'ASC') as in Figure 10.
There are 3 kinds of pagination methods also available
It's important if you are going to use setPage(1) to call setRange(75) first because the underlying function simply calculates the start index based on the range. Two other methods that are not covered by Figure 10 are the ability to group and to set the table to something else.Figure 11. Other Useful methods
When your happy with your query you can retrieve the results in 3 ways as described in Figure 12.Figure 12. Retrieving Results
->getTotal() ->getRows() ->getCollection()
Figure 12 shows 3 ways to get the results, the first way getTotal(), will retrieve the total number and does not consider pagination elements. getRows() will simply return a raw array. getCollection() will return you an object with the results for further manipulation. We will cover collections later in this section.« Back to C. SQLite 5. Models »