MIGXdb - Grid results issue | MODX Community Forums

☆ A M B ☆
222 Posts

Jason Sonderman Reply #1, 12 years, 5 months ago

New issue: my grid only shows 6 rows, even though it recognizes 10 total rows. It doesn't allow me to go to the next page, and the only way to have it show all 10 is to change the "results per page" from the default 10 to 20.

Why would it only want to show 6?

Here is the config

{
  "formtabs":[
    {
      "MIGX_id":3,
      "caption":"Product Lines",
      "print_before_tabs":1,
      "fields":[
        {
          "MIGX_id":1,
          "field":"name",
          "caption":"Product Line Name",
          "description":"",
          "description_is_code":"0",
          "inputTV":"",
          "inputTVtype":"",
          "validation":"",
          "configs":"",
          "sourceFrom":"config",
          "sources":"[]",
          "inputOptionValues":"",
          "default":""
        },
        {
          "MIGX_id":3,
          "field":"disabled",
          "caption":"Hide Product?",
          "description":"",
          "description_is_code":"0",
          "inputTV":"",
          "inputTVtype":"listbox",
          "validation":"",
          "configs":"",
          "restrictive_condition":"",
          "display":"",
          "sourceFrom":"config",
          "sources":"[]",
          "inputOptionValues":"No==0||Yes==1",
          "default":"0"
        },
        {
          "MIGX_id":2,
          "field":"priority",
          "caption":"List Priority",
          "description":"Set the priority when product lines are listed. Lower numbers are put at the top of the list. In the case of matching priorities, product lines will be sort alphabetically",
          "description_is_code":"0",
          "inputTV":"",
          "inputTVtype":"singleSlider",
          "validation":"",
          "configs":{
            "allowBlank":true,
            "allowDecimals":"No",
            "allowNegative":"No",
            "decimalPrecision":"0",
            "decimalSeparator":".",
            "maxValue":100,
            "minValue":"0"
          },
          "restrictive_condition":"",
          "display":"",
          "sourceFrom":"config",
          "sources":"[]",
          "inputOptionValues":"",
          "default":""
        },
        {
          "MIGX_id":4,
          "field":"video",
          "caption":"Video Link",
          "description":"",
          "description_is_code":"0",
          "inputTV":"",
          "inputTVtype":"",
          "validation":"",
          "configs":"",
          "restrictive_condition":"",
          "display":"",
          "sourceFrom":"config",
          "sources":"[]",
          "inputOptionValues":"",
          "default":""
        }
      ]
    },
    {
      "MIGX_id":4,
      "caption":"Products",
      "print_before_tabs":"0",
      "fields":[
        {
          "MIGX_id":1,
          "field":"products",
          "caption":"Products",
          "description":"",
          "description_is_code":"0",
          "inputTV":"",
          "inputTVtype":"migxdb",
          "validation":"",
          "configs":"Products",
          "restrictive_condition":"[[+id:is=``:then=`1`:else=`0`]]",
          "display":"",
          "sourceFrom":"config",
          "sources":"[]",
          "inputOptionValues":"",
          "default":""
        }
      ]
    },
    {
      "MIGX_id":6,
      "caption":"Overview",
      "print_before_tabs":"0",
      "fields":[
        {
          "MIGX_id":1,
          "field":"description",
          "caption":"Overview",
          "description":"",
          "description_is_code":"0",
          "inputTV":"cmprichtext",
          "inputTVtype":"",
          "validation":"",
          "configs":"",
          "restrictive_condition":"",
          "display":"",
          "sourceFrom":"config",
          "sources":"[]",
          "inputOptionValues":"",
          "default":""
        }
      ]
    },
    {
      "MIGX_id":5,
      "caption":"Product Groups",
      "print_before_tabs":"0",
      "fields":[
        {
          "MIGX_id":1,
          "field":"groups",
          "caption":"Groups",
          "description":"",
          "description_is_code":"0",
          "inputTV":"",
          "inputTVtype":"migxdb",
          "validation":"",
          "configs":"Product Groups",
          "restrictive_condition":"[[+id:is=``:then=`1`:else=`0`]]",
          "display":"",
          "sourceFrom":"config",
          "sources":"[]",
          "inputOptionValues":"",
          "default":""
        }
      ]
    },
    {
      "MIGX_id":8,
      "caption":"Product Categories",
      "print_before_tabs":"0",
      "fields":[
        {
          "MIGX_id":1,
          "field":"subcats",
          "caption":"Group Categories",
          "description":"These categories are exclusive to this product line. They define the groupings of individual products and the color groups. ",
          "description_is_code":"0",
          "inputTV":"",
          "inputTVtype":"migxdb",
          "validation":"",
          "configs":"Product Category",
          "restrictive_condition":"[[+id:is=``:then=`1`:else=`0`]]",
          "display":"",
          "sourceFrom":"config",
          "sources":"[]",
          "inputOptionValues":"",
          "default":""
        }
      ]
    }
  ],
  "contextmenus":"update||duplicate||remove",
  "actionbuttons":"addItem",
  "columnbuttons":"update||duplicate||remove",
  "filters":"[]",
  "extended":{
    "migx_add":"Add Product Line",
    "formcaption":"",
    "update_win_title":"",
    "win_id":"productline",
    "maxRecords":"",
    "addNewItemAt":"bottom",
    "multiple_formtabs":"",
    "actionbuttonsperrow":4,
    "winbuttonslist":"",
    "extrahandlers":"",
    "filtersperrow":4,
    "packageName":"rhythmcatalog",
    "classname":"productLine",
    "task":"productline",
    "getlistsort":"priority",
    "getlistsortdir":"",
    "use_custom_prefix":"0",
    "prefix":"",
    "grid":"",
    "gridload_mode":1,
    "check_resid":"0",
    "check_resid_TV":"",
    "join_alias":"ProductColorGroup",
    "has_jointable":"yes",
    "getlistwhere":"",
    "joins":"",
    "cmpmaincaption":"RhythmLink Catalog Manager",
    "cmptabcaption":"Product Lines",
    "cmptabdescription":"",
    "cmptabcontroller":"",
    "winbuttons":"",
    "onsubmitsuccess":"",
    "submitparams":""
  },
  "columns":[
    {
      "MIGX_id":4,
      "header":"Pri",
      "dataIndex":"priority",
      "width":5,
      "sortable":true,
      "show_in_grid":1,
      "renderer":"",
      "clickaction":"",
      "selectorconfig":"",
      "renderchunktpl":"",
      "renderoptions":"[]"
    },
    {
      "MIGX_id":1,
      "header":"id",
      "dataIndex":"id",
      "width":"",
      "sortable":"false",
      "show_in_grid":"0",
      "renderer":"",
      "clickaction":"",
      "selectorconfig":"",
      "renderchunktpl":"",
      "renderoptions":"[]"
    },
    {
      "MIGX_id":2,
      "header":"Product Line Name",
      "dataIndex":"name",
      "width":"",
      "sortable":true,
      "show_in_grid":1,
      "renderer":"",
      "clickaction":"",
      "selectorconfig":"",
      "renderchunktpl":"",
      "renderoptions":"[]"
    },
    {
      "MIGX_id":3,
      "header":"",
      "dataIndex":"deleted",
      "width":"",
      "sortable":"false",
      "show_in_grid":"0",
      "renderer":"",
      "clickaction":"",
      "selectorconfig":"",
      "renderchunktpl":"",
      "renderoptions":"[]"
    },
    {
      "MIGX_id":5,
      "header":"Actions",
      "dataIndex":"",
      "width":100,
      "sortable":"false",
      "show_in_grid":1,
      "renderer":"this.renderRowActions",
      "clickaction":"",
      "selectorconfig":"",
      "renderchunktpl":"",
      "renderoptions":"[]"
    }
  ]
}

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

☆ A M B ☆
222 Posts

Jason Sonderman Reply #2, 12 years, 5 months ago

BTW, the only way to get all 10 rows of data to show is to set the Per Page to 16 or higher.

5,888 Posts

Bruno17 Reply #3, 12 years, 5 months ago

what is the generated sql?
does it work, when you try to use that sql within phpmyadmin?

does it use a custom-getlist - processor here or the default one?

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

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

☆ A M B ☆
222 Posts

Jason Sonderman Reply #4, 12 years, 5 months ago

Where do I find the generated sql?

It uses the default getlist for migx as I don't need anything custom for this grid (aside from setting the default sort to the priority field).

5,888 Posts

Bruno17 Reply #5, 12 years, 5 months ago

Where do I find the generated sql?

uncomment this line and watch the firebug - console
https://github.com/Bruno17/MIGX/blob/master/core/components/migx/processors/mgr/default/getlist.php#L155

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

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

☆ A M B ☆
222 Posts

Jason Sonderman Reply #6, 12 years, 5 months ago

here is the sql for the grid in question:

SELECT `productLine`.`id`, `productLine`.`name`, `productLine`.`priority`, `productLine`.`description`, `productLine`.`disabled`, `productLine`.`video`, `productLine`.`published`, `productLine`.`createdby`, `productLine`.`createdon`, `productLine`.`deleted`, `productLine`.`deletedon`, `productLine`.`deletedby`, `productLine`.`publishedon`, `productLine`.`publishedby`, `ProductColorGroup`.`id` AS `Joined_id`, `ProductColorGroup`.`cg_id` AS `Joined_cg_id`, `ProductColorGroup`.`line_id` AS `Joined_line_id` FROM `modx_rli_product_line` AS `productLine` LEFT JOIN `modx_rli_line_cg_opts` `ProductColorGroup` ON `productLine`.`id` =  `ProductColorGroup`.`line_id` ORDER BY priority ASC LIMIT 10

5,888 Posts

Bruno17 Reply #7, 12 years, 5 months ago

does it list 10 rows, when you put that query into phpMyAdmin?

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

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

☆ A M B ☆
222 Posts

Jason Sonderman Reply #8, 12 years, 5 months ago

The phpmyadmin was returning 10 results, but 4 of the results were duplicates (one record was duplicated 4 times, another twice).

Looks like it has to do with that LeftJoin part. When I remove the Join Alias form the DB config, the problem clears up.

Since I keep thinking about the Join Alias incorrectly, do you use the Join Alias in the db configs for an aggregate or composite relationship?

Productline table has a composite relationship with ProductColorGroup (Product line can only have one color group, but a color group can belong to many Product lines), and ProductColor group likewise has an aggregate relationship with ProductLine.

discuss.answer

5,888 Posts

Bruno17 Reply #9, 12 years, 5 months ago

I'm using Join Alias only for nested grids, where I want to add childs with automatically determined join - id of the parent-table.

for simple joining (to get some fields of connected tables into the grid) I use the JOINS - field and put a relevant json into it.

Regards your issue with duplicates, it doesn't make any difference, whether its aggregate or composite.

Seems there is an issue with the relationships anywhere.
What is your current relationship-definition between ProductLine and ProductColorGroup?

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

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

☆ A M B ☆
222 Posts

Jason Sonderman Reply #10, 12 years, 5 months ago

ah, that wasn't clear that Join Alias was only intended for nested grids. Sounded like you could use Join Alias or Joins interchangeably.

As for my relations, here are the two chunks from the schema on the two tables

<object class="productLine" table="rli_product_line" extends="xPDOSimpleObject" >
	<field key="name" dbtype="varchar" precision="255" phptype="string" null="false" default="" index="index" />
	<field key="priority" dbtype="integer" precision="10" phptype="integer" attributes="unsigned" null="true" default="100"/>
	<field key="description" dbtype="mediumtext" phptype="string" null="true" default=""/>
	<field key="disabled" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0" />
	<field key="video" dbtype="varchar" precision="255" phptype="string" null="true" default=""/>
	<field key="published" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0" />
	<field key="createdby" dbtype="int" precision="10" phptype="integer" null="false" default="0" />
	<field key="createdon" dbtype="datetime" phptype="datetime" null="false" />
	<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="publishedon" dbtype="datetime" phptype="datetime" null="false" />
	<field key="publishedby" dbtype="int" precision="10" phptype="integer" null="false" default="0" />
	<index alias="PRIMARY" name="PRIMARY" primary="true" unique="true">
		<column key="id" collation="A" null="false" />
	</index>
	<composite alias="ProductColorGroup" class="productLineColorGroup" local="id" foreign="line_id" cardinality="many" owner="local"/>
<composite alias="Product" class="productInfo" local="id" foreign="line_id" cardinality="many" owner="local"/>
</object>
<object class="productLineColorGroup" table="rli_line_cg_opts" extends="xPDOSimpleObject" >
	<field key="cg_id" dbtype="tinyint" phptype="integer" attributes="unsigned" null="true" default="0"/>
	<field key="line_id" dbtype="tinyint" phptype="integer" attributes="unsigned" null="true" default="0" />
	<index alias="cg_id" name="cg_id" primary="false" unique="false" type="BTREE">
	        <column key="store" length="" collation="A" null="false" />
	    </index>
	    <index alias="line_id" name="line_id" primary="false" unique="false" type="BTREE">
	        <column key="owner" length="" collation="A" null="false" />
	    </index>
	<aggregate alias="ProductLine" class="productLine" local="line_id" foreign="id" cardinality="one" owner="foreign"/>
	<aggregate alias="ProductColorGroup" class="productColorGroup" local="cg_id" foreign="id" cardinality="one" owner="foreign"/>
</object>

Although it looks like we are changing some scope and that we will be dropping the productLineColorGroup table (it was a joining table between the Product Color Group table and Product line, but the client no longer wants to assign color groups to a product line).

Answered MIGXdb - Grid results issue