groupby / having query no longer working

  • Resolved robbiejackson

    (@robbiejackson)


    3 years ago

    We have a web site at https://www.walksinspain.org which is built around a database of Walks in Spain, implemented using Pods.

    Each “Walk” is a Custom Post Type, and is related via a Relationship field type to a Custom Taxonomy called “Area” which represents an area of Spain.
    The Area of a Walk is a mandatory field.

    In a couple of places, including the Search facility, we do a query to find which Areas contain Walks, as follows:

    $walkareas = pods('walk');
$params = array(
	'select' => 'walk_area.name as areaname, count(*) as total',
	'groupby' => "walk_area.name",
	'having' => 'count(*) > 0',
	'expiry' => 300);   // keep in cache for 5 minutes in case the user makes several searches
$walkareas->find($params);
while ($walkareas->fetch()) {
	$area_name = $walkareas->field('areaname');
	...
}

    where walk_area is the field of the Walks pod which relates to the Area record.

    The query returns a number of records, but for the last fetch() the areaname returned is a null string instead of ‘Pyrenees Central’.

    When I do other queries, eg to produce an export of the Walks, which includes the code

    $walk = pods( ' walk' );
$params = array(
	'limit' => -1,
	'orderby' => 'post_title');
$walk->find($params);
while ($walk->fetch()) {
	$title = $walk->field('post_title');
	$area = $walk->field('walk_area.name');
	...

    everything works as expected, and ‘Pyrenees Central’ is returned for walks in that area.

    This all did work in the recent past, but not sure when exactly it broke, but probably in one of the pods 2.8 releases.

    On my dev machine I have the same code but different data, and it works fine there, so I’m wondering has one of the internal pods records got corrupted.

    Is there some utility to check if all the internal data is consistent, and how would I go about trying to fix it if it were corrupt?

    I have tried clearing caches (including pods caches) and have tried changing the Area field of a walk to another area and then back again.

    Any ideas? – thanks for your help!

Viewing 3 replies - 1 through 3 (of 3 total)
  • Thread Starter robbiejackson

    (@robbiejackson)

    Just an update on this …

    I renamed the Area from ‘Pyrenees Central’ to ‘Pyrenees Central old’ and then created a new Area ‘Pyrenees Central’ from scratch with the same data as the old one.

    Then I updated all the walks which were related to (now) ‘Pyrenees Central old’ to set the walk_area to the new ‘Pyrenees Central’.

    This worked ok, and the query returned the right records for the new ‘Pyrenees Central’ except that when I changed a specific one of the walks it caused the query to return the incorrect results. This problem record was input some time ago – probably a few years ago.

    So I updated all of the other records to reference the new ‘Pyrenees Central’ and just left the one problem record pointing to ‘Pyrenees Central old’. This caused the query to work ok, except now there was no row returned relating to ‘Pyrenees Central old’, even though the problem record was still there pointing to it.

    I then created a new Walk record from scratch with the data of the problem record, and moved the problem record to the wordpress bin. And now things are working fine.

    So I’m happy to close this PR as it’s all now working ok, but can you let me know if you want any details of the offending record in order to troubleshoot what the cause of the problem might be. I reckon it must be something wrong in the pods internal wiring eg in wp_podsrel.

    Plugin Author Jory Hogeveen

    (@keraweb)

    Hi @robbiejackson

    Glad to hear you’ve fixed it. You mention wp_podsrel as the issue, I’m curious as to why you would think that? Are there records missing in this table?
    IF you could share more info with us about the problematic records, en then especially with a correct records for comparison that would be great!

    Cheers, Jory

    Thread Starter robbiejackson

    (@robbiejackson)

    Hi @keraweb

    I’ve compared the wp_posts, wp_terms, wp_postmeta and wp_podsrel records for the problematic Walk with those of a Walk where there’s no problem, and I can’t see anything unusual.

    However, I don’t really know the internals of pods, so I’m just looking at patterns in these tables, and the resolution of the query above may use some completely different mechanism.

    I’ll have to leave it there, but thanks for the support anyway.

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘groupby / having query no longer working’ is closed to new replies.