Element API - Filter on a value from a multi value field

Question

I am looking for some assistance with the Element API plugin. I need to filter on the bannerPageCategories field which is a multi select checkbox field. The value will be passed via a querystring value. I can filter on a field with a single value but how do I do this for fields which has an array of values.

Any suggestions will be greatly appreciated.

Sample code and json response is below.

Json Response:

    {
      title:"Test Banner 4",
      url:"http://localhost:8888/banners/test-banner-4",
      jsonUrl:"http://localhost:8888/api/banners/17",
      content:"Test.", 
       page-categories:[
       {
         label:"ADSL",
         value:"adsl",
         selected:true
       },
       {
        label:"Fibre",
        value:"fibre",
        selected:true
       }
    ],
   campaignId:"sHKJJK",
   targetUrl:"http://xyx.xaws.co.za",
   image1:"//xyx.xaws.co.za/dev/images/banners/banner-lg.png",
   image2:"//xyx.xaws.co.za/dev/images/banners/banner-md.png",
   image3:"//xyx.xaws.co.za/dev/images/banners/banner-sm.png",
   date_published:"2018-05-22T14:06:05+02:00",
   date_modified:"2018-05-22T14:06:05+02:00"
  }

Code:

    'api/banners' => function () {
        \Craft::$app->response->headers->set('Access-Control-Allow-Origin', '*');
        $target = \Craft::$app->request->getQueryParam('target');
        $campaign = \Craft::$app->request->getQueryParam('campaign');
        return [
            'elementType' => Entry::class,
            'criteria' => [
                'section' => 'banners',
                'bannerPageCategories' => $target,
                'campaignId' => $campaign              
            ],
            'transformer' => function (Entry $entry) {
                $image1 = $entry->image1->first();
                $image2 = $entry->image2->first();
                $image3 = $entry->image3->first();

                return [
                    'title' => $entry->title,
                    'url' => $entry->url,
                    'jsonUrl' => UrlHelper::url("api/banners/{$entry->id}"),
                    'content' => $entry->htmlcontent,
                    'page-categories' => $entry->bannerPageCategories,
                    'campaignId' => $entry->campaignId,
                    'targetUrl' => $entry->targetUrl,
                    'image1' => $image1 ? $image1->url : null,
                    'image2' => $image2 ? $image2->url : null,
                    'image3' => $image3 ? $image3->url : null,
                    'date_published' => $entry->postDate->format(\DateTime::ATOM),
                    'date_modified' => $entry->dateUpdated->format(\DateTime::ATOM),
                ];
            },
        ];
    },

Many thanks!

carlcs · Accepted Answer · 2018-05-22T14:07:15.703

2

When you wrap your term in asterisks, e.g. *news*, the generated SQL for this query parameter uses a LIKE operator for the ANDWHERE condition is uses (see Element Queries).

Make sure that the values for the Select field don’t overlap (sportnews and news both matching *news*).

'bannerPageCategories' => "*{$target}*"

edited May 22 '18 at 14:07

answered May 22 '18 at 13:47

carlcs

36,220
5
62
139

Thanks @carics, much appreciated. I am still a noob with craft. Based on the combination of your answer and Robins answer I ended up using : 'bannerPageCategories' => ['=', "{$target}"],. – Ray May 22 '18 at 19:09

score 1 · Answer 2 · answered May 22 '18 at 13:38

1

The value is stored as a serialized array in your database, in your case your would have to filter for the string "["adsl","fibre"]" (the order is the same like you specified in the field)

You can as well insert an array

['and',  '*adsl*', '*fibre*']

or

['or',  '*adsl*', '*fibre*']

answered May 22 '18 at 13:38

Robin Schambach

19,713
1
19
44

Thanks Robin. I ended up going with 'bannerPageCategories' => ['=', "{$target}"], which seemed to do the trick. – Ray May 22 '18 at 19:10

Element API - Filter on a value from a multi value field

2 Answers2

Linked

Related