Jump to content

Mix API with standard query


Frank Vèssia
 Share

Recommended Posts

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.

Link to comment
Share on other sites

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");
Link to comment
Share on other sites

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"); 
  • Like 1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...