Can't sort by one column in custom table.

5 Posts

nicholas warren Reply #1, 7 years, 3 months ago

Hi All
I have created a custom table using Bob's guide and script for doing so. Everything was working fine until I realised I needed to add a column to the table, which I did, after which I regenerated the schema and class files.

Now everything reads correctly I can pull the data from the column and I can sort by every column in the table bar the new one I have added.

Does anyone know why this might be the case? Have any ideas as to what I could try.

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

24,544 Posts

BobRay Reply #2, 7 years, 3 months ago

You've probably tried deleting all files in the core/cache directory, but if not, do that.

Do you have that new column in the SELECT part of your query?

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

5 Posts

nicholas warren Reply #3, 7 years, 3 months ago

I hadn't cleared the cache (my mistake), but it didn't change the outcome. I'm not sure how to make sure that it's in the SELECT part of the query. Would that be behind the scenes or in the php code in my snippet? Below is the code I'm using to do my sorting:

$q = $modx->newQuery('Blocks');
$q->sortby('Order','ASC');
$blocks = $modx->getCollection('Blocks', $q);

The table has four columns , ID which is the primary key, Name, Current and the new column Order. If I use the above code but put in ID, Name or Current it works and my page is populated with information. If I use Order for the sort my page loads but none of the data is populated, I just have the framework of the template.

24,544 Posts

BobRay Reply #4, 7 years, 3 months ago

I think the default select is all fields, so it should work as is.

Double-check your schema and the class and map files to make sure the order field is there. The field should have been added to the schema before you regenerated stuff.

Also, see if there's anything in the MODX error log.

As a diagnostic, you could try this in a snippet to see if the Order field shows up:

$block = $modx->getObject('Blocks');
$fields = $block->toArray();
return print_r($fields, true);

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

5 Posts

nicholas warren Reply #5, 7 years, 3 months ago

The error log is showing a related error:

[2017-01-23 17:08:16] (ERROR @ /home/morefhb/public_html/core/xpdo/om/xpdoobject.class.php : 240) Error 42000 executing statement: 
Array
(
    [0] => 42000
    [1] => 1064
    [2] => You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Order ASC' at line 1
)

I'm not sure what it's asking me though, as that is a system file right? so something in my generated classes is not playing nice with the xpdo object.

5 Posts

nicholas warren Reply #6, 7 years, 3 months ago

This is the schema file. Have I missed anything really obvious?

<?xml version="1.0" encoding="UTF-8"?>
<model package="medialibrary" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.1">
	<object class="Blocks" table="blocks" extends="xPDOObject">
		<field key="ID" dbtype="int" precision="11" phptype="integer" null="false" index="pk"  generated="native" />
		<field key="Name" dbtype="varchar" precision="250" phptype="string" null="false" />
		<field key="Current" dbtype="tinyint" precision="1" phptype="integer" null="false" />
		<field key="Order" dbtype="int" precision="11" phptype="integer" null="false" />

		<index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE" >
			<column key="ID" length="" collation="A" null="false" />
		</index>
		
		<composite alias="Children" class="Mediainfo" local="ID" foreign="Block" cardinality="many" owner="local"/>
		
	</object>
	<object class="Mediainfo" table="mediainfo" extends="xPDOObject">
		<field key="ID" dbtype="int" precision="11" phptype="integer" null="false" index="pk"  generated="native" />
		<field key="Path" dbtype="varchar" precision="250" phptype="string" null="false" />
		<field key="Title" dbtype="varchar" precision="250" phptype="string" null="false" />
		<field key="Speaker" dbtype="varchar" precision="50" phptype="string" null="false" />
		<field key="Date" dbtype="date" phptype="date" null="false" />
		<field key="Duration" dbtype="varchar" precision="8" phptype="string" null="false" />
		<field key="Description" dbtype="text" phptype="string" null="false" />
		<field key="Audio" dbtype="tinyint" precision="1" phptype="integer" null="false" />
		<field key="Resource" dbtype="varchar" precision="250" phptype="string" null="false" />
		<field key="Block" dbtype="int" precision="11" phptype="integer" null="false" index="index" />

		<index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE" >
			<column key="ID" length="" collation="A" null="false" />
		</index>
		<index alias="Block" name="Block" primary="false" unique="false" type="BTREE" >
			<column key="Block" length="" collation="A" null="false" />
		</index>
		
		<aggregate alias="Parent" class="Blocks" local="Block" foreign="ID" cardinality="one" owner="foreign"/>
		
	</object>
</model>

5,888 Posts

Bruno17 Reply #7, 7 years, 3 months ago

you have null="false" but no default in all of your fields. This might be an issue

And did you parse the schema again, after adding the field to the schema and did you add that field to your table?

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

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

discuss.answer

24,544 Posts

BobRay Reply #8, 7 years, 3 months ago

Here's another thought. "Order" is a reserved word in MySQL.

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

5 Posts

nicholas warren Reply #9, 7 years, 3 months ago

Thanks so much for your help Bob. Didn't know that Order was a reserved word, changed that to something different and now it works as expected.

24,544 Posts

BobRay Reply #10, 7 years, 3 months ago

I'm glad you got it sorted.

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

Answered Can't sort by one column in custom table.