Filtering a Column To A Name Using MIGX Loop Collection With &where

115 Posts

jimmyjazz Reply #1, 6 years, 11 months ago

Hi, I have a database with thousands of entries stored and what I am trying to do is use MIGX Loop Collection with an &where to filter a column called 'author' so it only shows the entries 'quote' and 'author' by the actual name used in the &where.

This is the code I am using:

[[!migxLoopCollection?
&packageName=`datequote`
&classname=`DateQuote`
&selectfields=`id,quote,author`
&where=`[{"author:>":"John Doe",}]`
&tpl=`authorTpl`
&sortConfig=`[{"sortby":"pos"}]`
]]
>

And this is my authorTpl:

<div class="grid-item">
<p style="font-size: 18px; color:#000000; font-weight: bold;">[[+quote]]</p>
<em style="font-size: 14px; color:#800000;">[[+author]]</em>
</div

In this example I am trying to display only the quotes by the author John Doe, however what I am finding is the whole database of thousands of authors is being displayed whereas there should only be 9 quotes displayed by this author.

Can anyone explain what I have done wrong and how to correct it?

This question has been answered by Bruno17. See the first response.

[ed. note: jimmyjazz last edited this post 6 years, 11 months ago.]

115 Posts

jimmyjazz Reply #2, 6 years, 11 months ago

I tried googling around for a solution but nothing I find so far has worked.

I found this 'Modx Cookbook Page':

http://modxcookbook.com/add-ons/migx/filtering-nested-migx-queries.html

Trying to understand how this works I tried a couple of adaptations but none seem to work, for example I tried this code but with this nothing appears on the front end:

[[!migxLoopCollection?
&packageName=`datequote`
&classname=`DateQuote`
&selectfields=`id,quote,author`
&where=`{"author":"John Doe","value:LIKE":"%\"date\":\"21\"%"}`
&tpl=`authorTpl`
&sortConfig=`[{"sortby":"pos"}]`
]]

This is the schema I am using;

<?xml version="1.0" encoding="UTF-8"?>
<model package="datequote" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.1">
    <object class="DateQuote" table="datequote" extends="xPDOSimpleObject">
	  	<field key="quote" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
		<field key="author" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
    	<field key="topic" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>        
		<field key="birthday" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
		<field key="month" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>		
		<field key="date" dbtype="int" precision="10" phptype="string" null="false" default=""/>
		<field key="createdon" dbtype="datetime" phptype="datetime" null="true"/>
    <field key="createdby" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
    <field key="editedon" dbtype="datetime" phptype="datetime" null="true"/>
    <field key="editedby" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
		<field key="deleted" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0" />
		<field key="deletedon" dbtype="datetime" phptype="datetime" null="false" />
		<field key="deletedby" dbtype="int" precision="10" phptype="integer" null="false" default="0" />
    <field key="published" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0" />  
		<field key="publishedon" dbtype="datetime" phptype="datetime" null="false" />
		<field key="publishedby" dbtype="int" precision="10" phptype="integer" null="false" default="0" />
    <aggregate alias="CreatedBy" class="modUser" local="createdby" foreign="id" cardinality="one" owner="foreign"/>
    <aggregate alias="EditedBy" class="modUser" local="editedby" foreign="id" cardinality="one" owner="foreign"/>
    </object>
</model>

For the person in this example 'John Doe' the 'date' field is 21 and the birthday is 21st May 1948.

I'm a writer not a coder so this part is a little bit beyond me, I kinda think what I'm trying to achieve here is probably quite easy but I just do not know.

discuss.answer

5,888 Posts

Bruno17 Reply #3, 6 years, 11 months ago

to get the author with name John Doe try:

&where=`{"author":"John Doe"}`

not sure, to understand, what you are trying with the date
but getting an author with name John Doe and date of 21, you would do:

&where=`{"author":"John Doe","date":"21"}`

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

115 Posts

jimmyjazz Reply #4, 6 years, 11 months ago

Quote from: Bruno17 at May 05, 2017, 05:36 PM

to get the author with name John Doe try:
&where=`{"author":"John Doe"}`
not sure, to understand, what you are trying with the date
but getting an author with name John Doe and date of 21, you would do:
&where=`{"author":"John Doe","date":"21"}`

Hi Bruno, I just tried the first one and it worked perfectly

Thank you so much

Answered Filtering a Column To A Name Using MIGX Loop Collection With &where