We launched new forums in March 2019—join us there. In a hurry for help with your website? Get Help Now!
    • 8274 ☆ A M B ☆
    • 222 Posts
    Here is the code that I am trying to work

    $c = $modx->newQuery('productInfo');
    $c->leftJoin('productLine','ProductLine', 'ProductLine.internalKey=productInfo.line_id');
    foreach($searchArr as $searchTerm){
     $whereArray[]=array('prod_no:LIKE' => '%'.$searchTerm.'%',
        'OR:options:LIKE' => '%'.$searchTerm.'%');   
    }
    $c->where(array($whereArray),xPDOQuery::SQL_OR);
    $products = $modx->getCollectionGraph('productInfo',array('ProductLine'=>array()), $c);


    I am working on a little custom search snippet for a couple of custom db tables productInfo contains a list of products that are related to the productLine table via the line_id field (which is the id of the productLine).

    Here is the schema
    <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="Product" class="productInfo" local="id" foreign="line_id" cardinality="many" owner="local"/>
    </object>
    <object class="productInfo" table="rli_products" extends="xPDOSimpleObject" >
    	<field key="prod_no" dbtype="varchar" precision="255" phptype="string" null="false" default="" index="index"/>
    	<field key="options" dbtype="mediumtext" phptype="string" null="true" default=""/>
    	<field key="qty" dbtype="integer" precision="10" phptype="integer" attributes="unsigned" null="true" default=""/>
    	<field key="so" dbtype="integer" precision="10" phptype="integer" attributes="unsigned" null="true" default=""/>
    	<field key="so_text" dbtype="varchar" precision="255" phptype="string" null="true" default="" index="index"/>
    	<field key="grp_id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0"/>
    	<field key="line_id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0"/>
    	<field key="category" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
    	<field key="colors" dbtype="tinyinteger" attributes="unsigned" phptype="integer" null="true" default=""/>
    	<field key="priority" dbtype="integer" precision="10" phptype="integer" attributes="unsigned" null="true" default="100"/>
    	<field key="disabled" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0" />
    	<field key="sample" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0" />
    	<field key="sample_qty" dbtype="tinyint" precision="4" 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" />
    	<index alias="PRIMARY" name="PRIMARY" primary="true" unique="true">
    		<column key="id" collation="A" null="false" />
    	</index>
    	<aggregate alias="ProductLine" class="productLine" local="line_id" foreign="id" cardinality="one" owner="foreign"/>
    </object>


    when I attempt a getCollectionGraph, this is the error that is in the log:
     [2014-04-23 13:15:27] (ERROR @ /index.php) Error 42000 executing query: SELECT `productInfo`.`id` AS `productInfo_id`, `productInfo`.`prod_no` AS `productInfo_prod_no`, `productInfo`.`options` AS `productInfo_options`, `productInfo`.`qty` AS `productInfo_qty`, `productInfo`.`so` AS `productInfo_so`, `productInfo`.`so_text` AS `productInfo_so_text`, `productInfo`.`grp_id` AS `productInfo_grp_id`, `productInfo`.`line_id` AS `productInfo_line_id`, `productInfo`.`category` AS `productInfo_category`, `productInfo`.`colors` AS `productInfo_colors`, `productInfo`.`priority` AS `productInfo_priority`, `productInfo`.`disabled` AS `productInfo_disabled`, `productInfo`.`sample` AS `productInfo_sample`, `productInfo`.`sample_qty` AS `productInfo_sample_qty`, `productInfo`.`createdby` AS `productInfo_createdby`, `productInfo`.`createdon` AS `productInfo_createdon`, `ProductLine`.`id` AS `ProductLine_id`, `ProductLine`.`name` AS `ProductLine_name`, `ProductLine`.`priority` AS `ProductLine_priority`, `ProductLine`.`description` AS `ProductLine_description`, `ProductLine`.`disabled` AS `ProductLine_disabled`, `ProductLine`.`video` AS `ProductLine_video`, `ProductLine`.`published` AS `ProductLine_published`, `ProductLine`.`createdby` AS `ProductLine_createdby`, `ProductLine`.`createdon` AS `ProductLine_createdon`, `ProductLine`.`deleted` AS `ProductLine_deleted`, `ProductLine`.`deletedon` AS `ProductLine_deletedon`, `ProductLine`.`deletedby` AS `ProductLine_deletedby`, `ProductLine`.`publishedon` AS `ProductLine_publishedon`, `ProductLine`.`publishedby` AS `ProductLine_publishedby` FROM `modx_rli_products` AS `productInfo` LEFT JOIN `modx_rli_product_line` `ProductLine` ON ProductLine.internalKey=productInfo.line_id LEFT JOIN `modx_rli_product_line` `ProductLine` ON `productInfo`.`line_id` = `ProductLine`.`id` WHERE  ( `productInfo`.`prod_no` LIKE ? OR `productInfo`.`options` LIKE ? )  ORDER BY `productInfo`.`id` ASC  - Array
    (
        [0] => 42000
        [1] => 1066
        [2] => Not unique table/alias: 'ProductLine'
    )


    What does this Not unique alias error mean?

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

    • discuss.answer
      • 8274 ☆ A M B ☆
      • 222 Posts
      This error was actually because of a getCollectionGraph. Not sure why getCollectionGraph couldn't work with joining tables, but I gave up and went with just a getCollection.

        • 3749
        • 24,544 Posts
        The problem may have been because the join is unnecessary with getCollectionGraph(). With the join, the alias might have appeared twice in the generated 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
          • 8274 ☆ A M B ☆
          • 222 Posts
          Probably. The hard part was the two tables did not have any aggregate or composite relationships, so the query join was needed to make the relationship with the two fields. I thought possibly getCollectionGraph would read the join, but as I found out not so much.