zajFetcher with custom queries

The zajFetcher interface is kept simple on purpose. You cannot do everything that you may need for a more complex query. When you have such queries, you can create a fetch() method with completely customized SQL.

Create a custom fetch() method

Any reusable SQL should be placed within the model definition file as a custom fetch() method. Custom fetch() methods are static, they return zajFetcher lists, and they have a fetch_by_descriptive_name naming convention. Otherwise they are just standard methods in your model like any other. See example below.

What fetch() methods do in the background

A standard fetch() method will run an SQL query in the background that gets the id’s of your result list. So, running Product::fetch(); will result in a query like follows:

SELECT id FROM product AS model ORDER BY ordernum DESC

If you add some filtering and limiting such as Product::fetch()->filter('name', 'Test')->limit(5); it’ll run:

SELECT id FROM product AS model WHERE `name`='Test' ORDER BY ordernum DESC LIMIT 5

Of course, all of this is performed in the background. But it is important to know this when you are creating completely customized queries.

You can always check the generated query by using the get_query() method:

print Product::fetch()->filter('name', 'Test')->limit(5)->get_query();

Custom SQL as subqueries

When you create custom queries you’ll need to run it as a subquery so that any modification that is automatically added to the query by the fetcher methods (like filter(), sort() or limit()) will be independent of your customized query. Without this you may get invalid SQL.

How to do this? Best if we show an example:

/**
 * Fetch the last visited products by order. Use Product::fetch_last_visited($order) instead for clarity.
 * @param Order|string|boolean $order the order. Defaults to the current order.
 * @return zajFetcher A list of products.
 */
public static function fetch_last_visited($order = false){
	// Set default
	if($order === false) $order = zajLib::me()->variable->shop->order;
	// Create custom query
	$sql = <<<EOF
		SELECT
			product.*, MAX(productvisit.time_create) as last_visited_by_user
		FROM
			ProductVisit, Product
		WHERE
			`order`='{$order->id}' AND product.id=productvisit.product GROUP BY product.id ORDER BY last_visited_by_user DESC
EOF;
	// Fetch products
	return Product::fetch()->sql($sql);
}

The sql() method creates a custom query and replaces the default fetcher SQL query with a subquery (using add_source()). It also disables the default ORDER BY with sort(false).

Use data from custom fetcher SQL queries

Though you have direct access to model data via the usual {{product.data.name}} properties you might also want to access the custom SELECTed data from your query, such as {{product.fetchdata.last_visited_by_user}} in the example above. See fetchdata property docs.

To access this data you must first add it as a field source:

Product::fetch()->sql($sql)->add_field_source("last_visited_by_user");

You can then access the data via the fetchdata property on each zajModel object:

{{product.fetchdata.last_visited_by_user}}