Jump to content

DatabaseQuery problem


MarkE
 Share

Recommended Posts

I am writing a method that extends WireSaveableItems (via a hook) to get a fresh item from the database. What I have so far is this:

	public function getFreshSaveableItem($event) {
		$saveables = $event->object;
		/* @var $saveables WireSaveableItems */
		$item = $event->arguments(0);
		$database = $this->wire()->database;
		$sql = $saveables->getLoadQuery()->getQuery();
		$query = $database->prepare($sql);
		$query->execute();
		$rows = $query->fetchAll(\PDO::FETCH_ASSOC);
		$freshItem = null;
		if($item) {
			$items =  new WireArray();
			foreach ($rows as $key => $val) {
				if ($val['id'] == $item->id) {
					$row = $rows[$key];
					$freshItem = $saveables->initItem($row, $items);
				}
			}
		}
		$saveableItems = array();
		foreach($items as $saveable) {
			$saveableItems = $saveableItems + $saveable->getArray();
		}
		$freshItem->setArray($saveableItems);
		$event->return = $freshItem;
	}

This works, except that where there are multiple matching rows (e.g. for fieldgroups), they are returned in the wrong order. This is because the rows include other fieldgroups and the sort order is 'sort' which will have duplicates because of the multiple fieldgroups.

To eliminate this problem, I wanted to put the selector inside the query - so that, if the item id is 168, it adds 'WHERE id=168' to the query. I implemented this by replacing

$sql = $saveables->getLoadQuery()->getQuery();

with

		$selector = "id=" . $item->id;
		$sql = $saveables->getLoadQuery($selector)->getQuery();

Unfortunately, these seems to generate the query 'WHERE id=:s0X' and I get a PDO exception 

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ':s0X 
ORDER BY sort'

I'm afraid my PDO/SQL skills are a bit limited, so I'm not quite sure what is causing this or how to fix it. I'm hoping someone with some higher-order PDO skills can help!

If it's any use, I did a bd() for the generated query and attach the Debug info and Full object images below.

602621383_Screenshot2023-08-05152342.png.6829744bc676ab2d7b62c9bed15d9457.png330779642_Screenshot2023-08-05152432.png.d8ce59fdcbeb1c1c879b673851c6c1f8.png

Link to comment
Share on other sites

I'm wondering if this is a bug. To test it simply put the following into the Tracy console (replacing 114 with the id of a template in your database):

$q = $templates->getLoadQuery("id=114");
d($q);
d($q->getQuery());

This generates the SQL query:

'SELECT templates.id,templates.name,templates.fieldgroups_id,templates.flags,templates.cache_time,templates.data  
FROM `templates`  
WHERE id=:s0X 
ORDER BY templates.name '

instead of WHERE id=114

Interestingly, the method WireSaveableItems::getLoadQuerySelectors() includes the following comment:

		// Note: ProcessWire core does not appear to ever reach this point as the
		// core does not use selectors to load any of its WireSaveableItems

which makes me wonder how well tested the use of selectors in this context is...

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...