Mix API with standard query

[Frank Vèssia]

Hello,

I created a "favorite page" system without using the page system of PW because using a page to store these info is to heavy.

The problem now is to mix a regular query select to the API. Is that possible?

some code...

$database = $pages->fuel('db');
$userID = $user->id;
$result = $database->query("SELECT `page_id` FROM `field_favorite` WHERE `created_user_id` = $userID");

$favs = $page->find("template=product,limit=20,sort=-created");

I want to connect the page_id result of my query to the PW query to get all my favorite pages.

[apeisa]

You might be looking for something like this:

$ids = "1001|1223|2311|3321";
$favs = $pages->find("id=$ids");

[Frank Vèssia]

mmm, i was thinking the same, but what about if i have hundreds of value? Is there any problem to select by ID in large amount?

$database = $pages->fuel('db');
$userID = $user->id;
$result = $database->query("SELECT `page_id` FROM `field_favorite` WHERE `created_user_id` = $userID")->fetch_assoc();

$listID = implode($result,"|");
$fav = $pages->find("id=$listID,limit=20,sort=-created");

[apeisa]

The query will not be a problem. But if you need to keep those pages in memory it might be. Then just add limit or pagination. But I wouldn't be too worried.

[Frank Vèssia]

Ok, infact I paginate the result, thanks!

[ryan]

You could also do this:

$result = wire('db')->query("SELECT `page_id` FROM `field_favorite` WHERE `created_user_id` = $user->id"); 
$ids = array();
while($row = $result->fetch_row()) $ids[] = list($row); 
$fav = $pages->getById($ids); 

Or, if 'favorite' is a ProcessWire field (which you maybe added a 'created_user_id' column to the table), you may be able to do this:

$fav = $pages->find("favorite.created_user_id=$user->id"); 

[Frank Vèssia]

mm Ryan I'm trying to use your first suggest, there is some syntax error in there?

The $fav variable is a pageArray ? If i want to set a limit in there?

[ryan]

I don't know about the syntax error... this is the query you originally posted, but I just changed $userID to $user->id. You might get rid of the `backtick` characters as they really aren't necessary. If this is a ProcessWire-created table, then the page_id field would be named pages_id (plural). But I don't know your table schema. If it's not a MySQL syntax error, then you might change $pages to wire('pages'), as $pages would only work in template scope.

For pagination, you'd want to do that in your your SQL query. Here's an updated example that uses pagination:

$limit = 25;
$start = (wire('input')->pageNum - 1) * $limit;
$sql = "SELECT page_id FROM field_favorite WHERE created_user_id=$user->id LIMIT $start,$limit"
$result = wire('db')->query($sql);
$ids = array();
while($row = $result->fetch_row()) $ids[] = $row[0];
$template = $templates->get('product');
$fav = $pages->getById($ids, $template);

Btw, if 'favorite' is by some chance actually a ProcessWire field you've created, you really would be better off just doing this:

$fav = wire('pages')->find("template=product, favorite.created_user_id=$user->id, limit=25"); 

---

edit: I think the syntax error must have been my use of list() before (just realized). Fixed in the above example.

[Frank Vèssia]

Thanks