XPDO MigX query assistance

Development
1

Hi all,

I’m hoping someone can can point me in the right direction. I’ve been banging my head against this wall for a couple days now :slight_smile:

I have a migx form entry set up that stores calendar events into a custom events table. This part is working perfectly - love it.

In this form, I have a multi-select “Event categories” field that let’s events be set to have multiple categories - to allow for filtering display on different ages and such.

image
image1004×595 31.1 KB

These values are stored double-pipe delimited in the database like “category-1||category-2” which is fine.

In my calendar setup options, I want to be able to have a “categories” property that can be user filled to contain one or multiple categories of events they want to display, and probably comma delimited for simplicity. So entering “category-1,category-3” would allow you to display any events that contain either of those selected categories.

So how do I do this in my xpdo query - which is working aces, save for this :slight_smile:

snippet below:

$thisStart = (isset ($_REQUEST['start'])) ? $_REQUEST['start'] : '';
$thisEnd = (isset ($_REQUEST['end'])) ? $_REQUEST['end'] : '';
$thisOrganization = (isset ($_REQUEST['organization'])) ? $_REQUEST['organization'] : '';
$thisCategory = (isset ($_REQUEST['category'])) ? $_REQUEST['category'] : '';

if (!$modx->addPackage('siteEvents',MODX_CORE_PATH.'components/siteEvents/model/','modx_')) {
   die('Can\'t load package, try again later.');
}

$class = 'SiteEvent';
#$output = "[";

$c = $modx->newQuery($class);

$c->cacheFlag === false;

$c->select('id, title, organization, organizationImg, startDate as start, url as eventURL, endDate as end, category, colour as color, description');

$c->where(
  array(
    'published' => '1',
    'startDate:>='=>$thisStart,
    'startDate:<'=>$thisEnd
));

if($thisOrganization){
    $thisOrganizationArray = explode(',', $thisOrganization);
    $c->where(array('AND:organization:IN'=>$thisOrganizationArray));
}

if($thisCategory){
    $thisCategoryArray = explode(',', $thisCategory);
    $x=0;
    foreach($thisCategoryArray as $item) {
    $x++;
      if($x<2){
          $c->where(array('AND:category:IN'=>$item));
      } else {
          $c->where(array('OR:category:IN'=>$item));
      }
    }
}

$c->sortby('startDate', 'ASC');

/* Get all rows */
$objects = $modx->getCollection($class, $c);

if (empty($objects)) {
   $output .= '';
}

$allEvents = [];
/* Iterate through rows */
foreach ($objects as $object) {
    array_push($allEvents, $object->toArray('',false,true));
}

$output = json_encode($allEvents);

return $output;

To help narrow it down, it’s this part that has me struggling - and it could be 180 degrees in the wrong direction… it’s probably my 10001 attempt at writing it :slight_smile:

if($thisCategory){
    $thisCategoryArray = explode(',', $thisCategory);
    $x=0;
    foreach($thisCategoryArray as $item) {
    $x++;
      if($x<2){
          $c->where(array('AND:category:IN'=>$item));
      } else {
          $c->where(array('OR:category:IN'=>$item));
      }
    }
}
2

Maybe you can make it work using LIKE

$c->where(array('AND:category:LIKE'=> '%' . $item . '%'));
...

or you read all categories from the database and do the filtering yourself in php in the foreach ($objects as $object) {...}-loop.

For a clean solution in SQL you would probably have to create new database tables (one for the categories and a intermediary table).

3

better, you would store all categories in its own table and have another table which holds all connections between the categories and the events with its ids only.

so, it would be a table with id, categoryname

and another table with id, category_id, event_id

4

Thanks Bruno - any chance I could trouble you for what the “$c->where…” line would look like with a related table setup and the record containing multiple pipe delimited values?

5

Thanks halftrainedharry!

I got it working using your example in this final form:

if($thisCategory){
    $thisCategoryArray = explode(',', $thisCategory);
    $x=0;
    foreach($thisCategoryArray as $item) {
        $x++;
        if($x < 2){
            $c->where(array('AND:category:LIKE'=> '%' . $item . '%'));
        } else {
            $c->where(array('OR:category:LIKE'=> '%' . $item . '%'));
        }
    }
}

I know using a related table would be ideal, but there’s never going to be more than five or six categories here max, and I have the categories user editable via a chunk. So this solution works - thanks again

6

be aware, this will not work correctly, if a category is partially named like another category.
For example, if you would have rowing and indoorrowing as category-names and you would search for ‘rowing’, this would find events for both, events with rowing and events with category indoorrowing

7

Thanks for the warning Bruno - good advice and I had already considered. I do have some control over the categories so I think this is unlikely to be an issue.

I do want to thank you for your help and work - migX is a lifesaver and has added so much possibility and flexibility to my projects! I see you posting help in here and other places for everyone’s benefit and I just want to thank you for your time! Cheers!

8

The whole point of additional tables would be, that your data isn’t stored as ‘pipe delimited values’ anymore. Every event-category-connection would be a row in the new table. You could then for example query it with getCollectionGraph

$events = $modx->getCollectionGraph('SiteEvent', '{"Categories":{}}', array('Categories.category_id:IN' => array(1,2)));
9

I JUST started to get comfortable using migx with a single custom table for data entry. I have no idea how to add a second table using migx schema/form for a single multi-select in my config… Lol. I guess you never stop learning right? :slight_smile: