[SOLVED] xpdo left join issues

- 125 Posts
ficklelife Reply #1, 14 years, 1 month ago
I am trying to left join two tables, the first time I have donte it and I am running into some issues! My query:
```
$query = $xpdo->newQuery('ShopProducts');
$query->select('ShopProducts.*, DbFiles.*');
$query->leftJoin('ShopProducts', 'DbFiles', array('ShopProducts.id = DbFiles.master_object_id'));
$query->where(array(
    'description:!=' => null
));
$query->sortby('rand()');
$query->limit(4);
```
now this generates this sql:
```
SELECT ShopProducts.*, DbFiles.* FROM `shop_products` AS `ShopProducts` LEFT JOIN `shop_products` `DbFiles` ON ShopProducts.id = DbFiles.master_object_id WHERE `ShopProducts`.`description` IS NOT NULL ORDER BY rand() ASC LIMIT 4
```
I have used CMP generator to generate schema and all seems well. a couple of issues with it though, it doesn't seem to generate the "id" column, so when I try to join using master_object_id in DbFiles to id in ShopProducts, id in either schema isn't defined. Would this cause a problem?

In CMP generator i didnt define a prefix as one table is shop_products and the other is db_files in the same database - could this cause an issue?

The generated SQL isn't clean either, shouldn't it not use the real table names?
[ed. note: ficklelife last edited this post 14 years ago.]
- 5,888 Posts
Bruno17 Reply #2, 14 years, 1 month ago

can you give a sql-dump of your table-structure and your xml-schema?
-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

125 Posts

ficklelife Reply #3, 14 years, 1 month ago

Hi Bruno,

My schema:

<?xml version="1.0" encoding="UTF-8"?>
<model package="lemony" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.1">
	<object class="DbFiles" table="db_files" extends="xPDOSimpleObject">
		<field key="mime_type" dbtype="varchar" precision="255" phptype="string" null="true" />
		<field key="size" dbtype="int" precision="11" phptype="integer" null="true" />
		<field key="description" dbtype="varchar" precision="255" phptype="string" null="true" />
		<field key="master_object_class" dbtype="varchar" precision="255" phptype="string" null="true" index="index" />
		<field key="master_object_id" dbtype="int" precision="11" phptype="integer" null="true" />
		<field key="created_at" dbtype="datetime" phptype="datetime" null="true" />
		<field key="created_user_id" dbtype="int" precision="11" phptype="integer" null="true" />
		<field key="name" dbtype="varchar" precision="255" phptype="string" null="true" index="index" />
		<field key="disk_name" dbtype="varchar" precision="255" phptype="string" null="true" />
		<field key="field" dbtype="varchar" precision="100" phptype="string" null="true" index="index" />
		<field key="is_public" dbtype="tinyint" precision="4" phptype="integer" null="true" />
		<field key="sort_order" dbtype="int" precision="11" phptype="integer" null="true" />
		<field key="title" dbtype="varchar" precision="255" phptype="string" null="true" />

		<index alias="master_index" name="master_index" primary="false" unique="false" type="BTREE" >
			<column key="master_object_class" length="" collation="A" null="true" />
			<column key="master_object_id" length="" collation="A" null="true" />
		</index>
		<index alias="file_filed" name="file_filed" primary="false" unique="false" type="BTREE" >
			<column key="field" length="" collation="A" null="true" />
		</index>
		<index alias="file_name" name="file_name" primary="false" unique="false" type="BTREE" >
			<column key="name" length="" collation="A" null="true" />
		</index>
		<composite alias="ShopProducts" class="ShopProducts" local="master_object_id" foreign="id" cardinality="one" owner="foreign" />
		
		
		
		
	</object>
	<object class="ShopProducts" table="shop_products" extends="xPDOSimpleObject">
		<field key="name" dbtype="varchar" precision="255" phptype="string" null="true" index="index" />
		<field key="description" dbtype="text" phptype="string" null="true" />
		<field key="short_description" dbtype="text" phptype="string" null="true" />
		<field key="url_name" dbtype="varchar" precision="255" phptype="string" null="true" index="index" />
		<field key="price" dbtype="decimal" precision="15,2" phptype="float" null="true" index="index" />
		<field key="sku" dbtype="varchar" precision="100" phptype="string" null="true" index="index" />
		<field key="weight" dbtype="float" phptype="float" null="true" index="index" />
		<field key="width" dbtype="float" phptype="float" null="true" index="index" />
		<field key="height" dbtype="float" phptype="float" null="true" index="index" />
		<field key="depth" dbtype="float" phptype="float" null="true" index="index" />
		<field key="manufacturer_id" dbtype="int" precision="11" phptype="integer" null="true" index="index" />
		<field key="meta_description" dbtype="text" phptype="string" null="true" />
		<field key="meta_keywords" dbtype="text" phptype="string" null="true" />
		<field key="on_sale" dbtype="tinyint" precision="4" phptype="integer" null="true" />
		<field key="enabled" dbtype="tinyint" precision="4" phptype="integer" null="true" index="index" />
		<field key="track_inventory" dbtype="tinyint" precision="4" phptype="integer" null="true" index="index" />
		<field key="in_stock" dbtype="int" precision="11" phptype="integer" null="true" index="index" />
		<field key="hide_if_out_of_stock" dbtype="tinyint" precision="4" phptype="integer" null="true" index="index" />
		<field key="stock_alert_threshold" dbtype="int" precision="11" phptype="integer" null="true" index="index" />
		<field key="allow_pre_order" dbtype="tinyint" precision="4" phptype="integer" null="true" />
		<field key="created_user_id" dbtype="int" precision="11" phptype="integer" null="true" index="index" />
		<field key="updated_user_id" dbtype="int" precision="11" phptype="integer" null="true" index="index" />
		<field key="created_at" dbtype="datetime" phptype="datetime" null="true" />
		<field key="updated_at" dbtype="datetime" phptype="datetime" null="true" />
		<field key="page_id" dbtype="int" precision="11" phptype="integer" null="true" index="index" />
		<field key="product_id" dbtype="int" precision="11" phptype="integer" null="true" index="index" />
		<field key="grouped_option_desc" dbtype="varchar" precision="255" phptype="string" null="true" />
		<field key="grouped" dbtype="tinyint" precision="4" phptype="integer" null="true" index="index" />
		<field key="grouped_attribute_name" dbtype="varchar" precision="100" phptype="string" null="true" />
		<field key="tax_class_id" dbtype="int" precision="11" phptype="integer" null="true" index="index" />
		<field key="product_type_id" dbtype="int" precision="11" phptype="integer" null="true" index="index" />
		<field key="xml_data" dbtype="text" phptype="string" null="true" />
		<field key="tier_prices_per_customer" dbtype="tinyint" precision="4" phptype="integer" null="true" />
		<field key="tier_price_compiled" dbtype="text" phptype="string" null="true" />
		<field key="price_rules_compiled" dbtype="text" phptype="string" null="true" />
		<field key="grouped_sort_order" dbtype="int" precision="11" phptype="integer" null="true" />
		<field key="grouped_self_ghost_flag" dbtype="tinyint" precision="4" phptype="integer" null="true" />
		<field key="pt_description" dbtype="text" phptype="string" null="true" />
		<field key="expected_availability_date" dbtype="date" phptype="date" null="true" />
		<field key="enable_customer_group_filter" dbtype="tinyint" precision="4" phptype="integer" null="true" />
		<field key="disable_completely" dbtype="tinyint" precision="4" phptype="integer" null="true" index="index" />
		<field key="enable_perproduct_shipping_cost" dbtype="tinyint" precision="4" phptype="integer" null="true" />
		<field key="perproduct_shipping_cost" dbtype="text" phptype="string" null="true" />
		<field key="price_rule_map_compiled" dbtype="text" phptype="string" null="true" />
		<field key="cost" dbtype="decimal" precision="15,2" phptype="float" null="true" />
		<field key="title" dbtype="varchar" precision="255" phptype="string" null="true" />
		<field key="allow_negative_stock_values" dbtype="tinyint" precision="4" phptype="integer" null="true" />
		<field key="product_rating" dbtype="float" phptype="float" null="true" />
		<field key="product_rating_all" dbtype="float" phptype="float" null="true" />
		<field key="product_rating_review_num" dbtype="int" precision="11" phptype="integer" null="true" />
		<field key="product_rating_all_review_num" dbtype="int" precision="11" phptype="integer" null="true" />
		<field key="visibility_search" dbtype="tinyint" precision="4" phptype="integer" null="true" default="1" index="index" />
		<field key="visibility_catalog" dbtype="tinyint" precision="4" phptype="integer" null="true" default="1" index="index" />
		<field key="perproduct_shipping_cost_use_parent" dbtype="tinyint" precision="4" phptype="integer" null="true" />
		<field key="sale_price_or_discount" dbtype="varchar" precision="50" phptype="string" null="true" />
		<field key="price_index_compiled" dbtype="text" phptype="string" null="true" />
		<field key="total_in_stock" dbtype="int" precision="11" phptype="integer" null="true" />
		<field key="x_googleproducts_condition" dbtype="varchar" precision="255" phptype="string" null="true" />
		<field key="x_googleproducts_brand" dbtype="varchar" precision="255" phptype="string" null="true" />
		<field key="x_googleproducts_gtin" dbtype="varchar" precision="255" phptype="string" null="true" />
		<field key="x_googleproducts_mpn" dbtype="varchar" precision="255" phptype="string" null="true" />
		<field key="x_googleproducts_gender" dbtype="varchar" precision="255" phptype="string" null="true" />
		<field key="x_googleproducts_age_group" dbtype="varchar" precision="255" phptype="string" null="true" />
		<field key="x_googleproducts_color" dbtype="varchar" precision="255" phptype="string" null="true" />
		<field key="x_googleproducts_size" dbtype="varchar" precision="255" phptype="string" null="true" />
		<field key="x_googleproducts_included" dbtype="tinyint" precision="4" phptype="integer" null="true" default="1" />
		<field key="x_googleproducts_description" dbtype="text" phptype="string" null="true" />

		<index alias="url_name" name="url_name" primary="false" unique="false" type="BTREE" >
			<column key="url_name" length="" collation="A" null="true" />
		</index>
		<index alias="product_sku" name="product_sku" primary="false" unique="false" type="BTREE" >
			<column key="sku" length="" collation="A" null="true" />
		</index>
		<index alias="product_name" name="product_name" primary="false" unique="false" type="BTREE" >
			<column key="name" length="" collation="A" null="true" />
		</index>
		<index alias="product_price" name="product_price" primary="false" unique="false" type="BTREE" >
			<column key="price" length="" collation="A" null="true" />
		</index>
		<index alias="product_weight" name="product_weight" primary="false" unique="false" type="BTREE" >
			<column key="weight" length="" collation="A" null="true" />
		</index>
		<index alias="product_width" name="product_width" primary="false" unique="false" type="BTREE" >
			<column key="width" length="" collation="A" null="true" />
		</index>
		<index alias="product_height" name="product_height" primary="false" unique="false" type="BTREE" >
			<column key="height" length="" collation="A" null="true" />
		</index>
		<index alias="product_depth" name="product_depth" primary="false" unique="false" type="BTREE" >
			<column key="depth" length="" collation="A" null="true" />
		</index>
		<index alias="manufacturer_id" name="manufacturer_id" primary="false" unique="false" type="BTREE" >
			<column key="manufacturer_id" length="" collation="A" null="true" />
		</index>
		<index alias="manufacturer" name="manufacturer" primary="false" unique="false" type="BTREE" >
			<column key="manufacturer_id" length="" collation="A" null="true" />
		</index>
		<index alias="page_id" name="page_id" primary="false" unique="false" type="BTREE" >
			<column key="page_id" length="" collation="A" null="true" />
		</index>
		<index alias="product_type_id" name="product_type_id" primary="false" unique="false" type="BTREE" >
			<column key="product_type_id" length="" collation="A" null="true" />
		</index>
		<index alias="tax_class_id" name="tax_class_id" primary="false" unique="false" type="BTREE" >
			<column key="tax_class_id" length="" collation="A" null="true" />
		</index>
		<index alias="product_id" name="product_id" primary="false" unique="false" type="BTREE" >
			<column key="product_id" length="" collation="A" null="true" />
		</index>
		<index alias="grouped" name="grouped" primary="false" unique="false" type="BTREE" >
			<column key="grouped" length="" collation="A" null="true" />
		</index>
		<index alias="disable_completely" name="disable_completely" primary="false" unique="false" type="BTREE" >
			<column key="disable_completely" length="" collation="A" null="true" />
		</index>
		<index alias="created_user_id" name="created_user_id" primary="false" unique="false" type="BTREE" >
			<column key="created_user_id" length="" collation="A" null="true" />
		</index>
		<index alias="updated_user_id" name="updated_user_id" primary="false" unique="false" type="BTREE" >
			<column key="updated_user_id" length="" collation="A" null="true" />
		</index>
		<index alias="enabled" name="enabled" primary="false" unique="false" type="BTREE" >
			<column key="enabled" length="" collation="A" null="true" />
		</index>
		<index alias="track_inventory" name="track_inventory" primary="false" unique="false" type="BTREE" >
			<column key="track_inventory" length="" collation="A" null="true" />
		</index>
		<index alias="hide_if_out_of_stock" name="hide_if_out_of_stock" primary="false" unique="false" type="BTREE" >
			<column key="hide_if_out_of_stock" length="" collation="A" null="true" />
		</index>
		<index alias="stock_alert_threshold" name="stock_alert_threshold" primary="false" unique="false" type="BTREE" >
			<column key="stock_alert_threshold" length="" collation="A" null="true" />
		</index>
		<index alias="in_stock" name="in_stock" primary="false" unique="false" type="BTREE" >
			<column key="in_stock" length="" collation="A" null="true" />
		</index>
		<index alias="visibility_search" name="visibility_search" primary="false" unique="false" type="BTREE" >
			<column key="visibility_search" length="" collation="A" null="true" />
		</index>
		<index alias="visibility_catalog" name="visibility_catalog" primary="false" unique="false" type="BTREE" >
			<column key="visibility_catalog" length="" collation="A" null="true" />
		</index>
		
		<composite alias="DbFiles" class="DbFiles" local="id" foreign="master_object_id" cardinality="many" owner="local" />
	</object>
</model>

structure fro db_files:


Column Name            Type        Size    Primary Key    Foreign Key    Nullable    Scale
id                     int                 true                          NO               
mime_type              varchar     255                                   YES              
size                   int                                               YES              
description            varchar     255                                   YES              
master_object_class    varchar     255                                   YES              
master_object_id       int                                               YES              
created_at             datetime                                          YES              
created_user_id        int                                               YES              
name                   varchar     255                                   YES              
disk_name              varchar     255                                   YES              
field                  varchar     100                                   YES              
is_public              tinyint                                           YES              
sort_order             int                                               YES              
title                  varchar     255                                   YES

Structure for shop_products:


Column Name                            Type        Size     Primary Key    Foreign Key    Nullable    Scale
id                                     int                  true                          NO               
name                                   varchar     255                                    YES              
description                            text        65535                                  YES              
short_description                      text        65535                                  YES              
url_name                               varchar     255                                    YES              
price                                  decimal     15                                     YES         2    
sku                                    varchar     100                                    YES              
weight                                 float                                              YES              
width                                  float                                              YES              
height                                 float                                              YES              
depth                                  float                                              YES              
manufacturer_id                        int                                                YES              
meta_description                       text        65535                                  YES              
meta_keywords                          text        65535                                  YES              
on_sale                                tinyint                                            YES              
enabled                                tinyint                                            YES              
track_inventory                        tinyint                                            YES              
in_stock                               int                                                YES              
hide_if_out_of_stock                   tinyint                                            YES              
stock_alert_threshold                  int                                                YES              
allow_pre_order                        tinyint                                            YES              
created_user_id                        int                                                YES              
updated_user_id                        int                                                YES              
created_at                             datetime                                           YES              
updated_at                             datetime                                           YES              
page_id                                int                                                YES              
product_id                             int                                                YES              
grouped_option_desc                    varchar     255                                    YES              
grouped                                tinyint                                            YES              
grouped_attribute_name                 varchar     100                                    YES              
tax_class_id                           int                                                YES              
product_type_id                        int                                                YES              
xml_data                               text        65535                                  YES              
tier_prices_per_customer               tinyint                                            YES              
tier_price_compiled                    text        65535                                  YES              
price_rules_compiled                   text        65535                                  YES              
grouped_sort_order                     int                                                YES              
grouped_self_ghost_flag                tinyint                                            YES              
pt_description                         text        65535                                  YES              
expected_availability_date             date                                               YES              
enable_customer_group_filter           tinyint                                            YES              
disable_completely                     tinyint                                            YES              
enable_perproduct_shipping_cost        tinyint                                            YES              
perproduct_shipping_cost               text        65535                                  YES              
price_rule_map_compiled                text        65535                                  YES              
cost                                   decimal     15                                     YES         2    
title                                  varchar     255                                    YES              
allow_negative_stock_values            tinyint                                            YES              
product_rating                         float                                              YES              
product_rating_all                     float                                              YES              
product_rating_review_num              int                                                YES              
product_rating_all_review_num          int                                                YES              
visibility_search                      tinyint                                            YES              
visibility_catalog                     tinyint                                            YES              
perproduct_shipping_cost_use_parent    tinyint                                            YES              
sale_price_or_discount                 varchar     50                                     YES              
price_index_compiled                   text        65535                                  YES              
total_in_stock                         int                                                YES              
x_googleproducts_condition             varchar     255                                    YES              
x_googleproducts_brand                 varchar     255                                    YES              
x_googleproducts_gtin                  varchar     255                                    YES              
x_googleproducts_mpn                   varchar     255                                    YES              
x_googleproducts_gender                varchar     255                                    YES              
x_googleproducts_age_group             varchar     255                                    YES              
x_googleproducts_color                 varchar     255                                    YES              
x_googleproducts_size                  varchar     255                                    YES              
x_googleproducts_included              tinyint                                            YES              
x_googleproducts_description           text        65535                                  YES

- 125 Posts
ficklelife Reply #4, 14 years, 1 month ago

Just a bit more of what the objective is:

within the table db_files, in the field "disk_name" contains the URL of a jpg file. The field "master_object_id " contains the id of the item it is linked to in the table shop_products

So basically I want all shop_product.*, with the corresonding JPG filename that is contained within db_files.disk_name field, using db_files.master_object_id matched to shop_products.id
[ed. note: ficklelife last edited this post 14 years, 1 month ago.]

5,888 Posts

Bruno17 Reply #5, 14 years, 1 month ago

created a new package 'lemony' and added your schema, created the tables from schema

(took me less than 5 minutes with MIGXdb's package manager)

could get the records with this snippet:

<?php

$packageName = 'lemony';

$packagepath = $modx->getOption('core_path') . 'components/' . $packageName . '/';
$modelpath = $packagepath . 'model/';

$modx->addPackage($packageName, $modelpath);

$query = $modx->newQuery('ShopProducts');
$query->select($modx->getSelectColumns('ShopProducts','ShopProducts',''));
$query->leftJoin('DbFiles', 'DbFiles' );
$query->select($modx->getSelectColumns('DbFiles','DbFiles','DbFiles_'));
$query->where(array(
    'ShopProducts.description:!=' => null
));
$query->sortby('rand()');
$query->limit(4);

$query->prepare();echo $query->toSql();

if ($collection = $modx->getCollection('ShopProducts',$query)){
    foreach ($collection as $object){
       $items[] = $object->toArray();
       
    }
    echo '<pre>' . print_r($items,1) .'</pre>';
}

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

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

125 Posts

ficklelife Reply #6, 14 years, 1 month ago

Thanks for your help Bruno. Still having some issues. I am fetching these from a different database - here is my code, mixed with yours.

<?php
$dsn = 'mysql:host=localhost;dbname=centurio_lemonstand;port=3306;charset=utf-8';
$xpdo = new xPDO($dsn,'xxxxxxx','xxxxxxx');

// check to see if connected to external db

echo $o=($xpdo->connect()) ? 'Connected' : 'Not Connected';

// add package
$path = MODX_CORE_PATH . 'components/lemony/';
$result = $xpdo->addPackage('lemony',$path . 'model/','');

// check to see if package loaded
if (!$result) {
  echo 'failed to add package';
} else {
  echo '<p>added Package</p>';
}

$query = $xpdo->newQuery('ShopProducts');
$query->select($xpdo->getSelectColumns('ShopProducts','ShopProducts',''));
$query->leftJoin('DbFiles', 'DbFiles' );
$query->select($xpdo->getSelectColumns('DbFiles','DbFiles','DbFiles_'));
$query->where(array(
    'ShopProducts.description:!=' => null
));
$query->sortby('rand()');
$query->limit(4);
 
$query->prepare();echo '<pre>'.$query->toSql().'</pre>';
 
if ($collection = $xpdo->getCollection('ShopProducts',$query)){
    foreach ($collection as $object){
       $items[] = $object->toArray();
        
    }
    echo '<pre>' . print_r($items,1) .'</pre>';
}

The resulting SQL statement is :

SELECT `ShopProducts`.`id`, `ShopProducts`.`name`, `ShopProducts`.`description`, `ShopProducts`.`short_description`, `ShopProducts`.`url_name`, `ShopProducts`.`price`, `ShopProducts`.`sku`, `ShopProducts`.`weight`, `ShopProducts`.`width`, `ShopProducts`.`height`, `ShopProducts`.`depth`, `ShopProducts`.`manufacturer_id`, `ShopProducts`.`meta_description`, `ShopProducts`.`meta_keywords`, `ShopProducts`.`on_sale`, `ShopProducts`.`enabled`, `ShopProducts`.`track_inventory`, `ShopProducts`.`in_stock`, `ShopProducts`.`hide_if_out_of_stock`, `ShopProducts`.`stock_alert_threshold`, `ShopProducts`.`allow_pre_order`, `ShopProducts`.`created_user_id`, `ShopProducts`.`updated_user_id`, `ShopProducts`.`created_at`, `ShopProducts`.`updated_at`, `ShopProducts`.`page_id`, `ShopProducts`.`product_id`, `ShopProducts`.`grouped_option_desc`, `ShopProducts`.`grouped`, `ShopProducts`.`grouped_attribute_name`, `ShopProducts`.`tax_class_id`, `ShopProducts`.`product_type_id`, `ShopProducts`.`xml_data`, `ShopProducts`.`tier_prices_per_customer`, `ShopProducts`.`tier_price_compiled`, `ShopProducts`.`price_rules_compiled`, `ShopProducts`.`grouped_sort_order`, `ShopProducts`.`grouped_self_ghost_flag`, `ShopProducts`.`pt_description`, `ShopProducts`.`expected_availability_date`, `ShopProducts`.`enable_customer_group_filter`, `ShopProducts`.`disable_completely`, `ShopProducts`.`enable_perproduct_shipping_cost`, `ShopProducts`.`perproduct_shipping_cost`, `ShopProducts`.`price_rule_map_compiled`, `ShopProducts`.`cost`, `ShopProducts`.`title`, `ShopProducts`.`allow_negative_stock_values`, `ShopProducts`.`product_rating`, `ShopProducts`.`product_rating_all`, `ShopProducts`.`product_rating_review_num`, `ShopProducts`.`product_rating_all_review_num`, `ShopProducts`.`visibility_search`, `ShopProducts`.`visibility_catalog`, `ShopProducts`.`perproduct_shipping_cost_use_parent`, `ShopProducts`.`sale_price_or_discount`, `ShopProducts`.`price_index_compiled`, `ShopProducts`.`total_in_stock`, `ShopProducts`.`x_googleproducts_condition`, `ShopProducts`.`x_googleproducts_brand`, `ShopProducts`.`x_googleproducts_gtin`, `ShopProducts`.`x_googleproducts_mpn`, `ShopProducts`.`x_googleproducts_gender`, `ShopProducts`.`x_googleproducts_age_group`, `ShopProducts`.`x_googleproducts_color`, `ShopProducts`.`x_googleproducts_size`, `ShopProducts`.`x_googleproducts_included`, `ShopProducts`.`x_googleproducts_description`, `DbFiles`.`id` AS `DbFiles_id`, `DbFiles`.`mime_type` AS `DbFiles_mime_type`, `DbFiles`.`size` AS `DbFiles_size`, `DbFiles`.`description` AS `DbFiles_description`, `DbFiles`.`master_object_class` AS `DbFiles_master_object_class`, `DbFiles`.`master_object_id` AS `DbFiles_master_object_id`, `DbFiles`.`created_at` AS `DbFiles_created_at`, `DbFiles`.`created_user_id` AS `DbFiles_created_user_id`, `DbFiles`.`name` AS `DbFiles_name`, `DbFiles`.`disk_name` AS `DbFiles_disk_name`, `DbFiles`.`field` AS `DbFiles_field`, `DbFiles`.`is_public` AS `DbFiles_is_public`, `DbFiles`.`sort_order` AS `DbFiles_sort_order`, `DbFiles`.`title` AS `DbFiles_title` FROM `shop_products` AS `ShopProducts` LEFT JOIN `db_files` `DbFiles` ON  WHERE `ShopProducts`.`description` IS NOT NULL ORDER BY rand() ASC LIMIT 4

Which doesn't return any results. It doesn't mention anything about shop_products table until the very end of the query.... I'll spend some more time this eve and will pull it apart and try to understand the xpdo query...

- 5,888 Posts
Bruno17 Reply #7, 14 years, 1 month ago
did you try to run the generated sql-statement directly to your db (in phpmyadmin, for example)

[Edit] I see there is missing the on - part
```
LEFT JOIN `db_files` `DbFiles` ON ...... WHERE
```
this is normaly created automatically since you have:
```
<composite alias="DbFiles" class="DbFiles" local="id" foreign="master_object_id" cardinality="many" owner="local" />
```
in your schema [ed. note: Bruno17 last edited this post 14 years, 1 month ago.]
-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

125 Posts

ficklelife Reply #8, 14 years, 1 month ago

I've added this to the missing ..... in the SQL:

ON (`ShopProducts`.`id` = `DbFiles`.`master_object_id`) WHERE

And it fetches everything as it should! Below is the full SQL query that works

SELECT `ShopProducts`.`id`, `ShopProducts`.`name`, `ShopProducts`.`description`, `ShopProducts`.`short_description`, `ShopProducts`.`url_name`, `ShopProducts`.`price`, `ShopProducts`.`sku`, `ShopProducts`.`weight`, `ShopProducts`.`width`, `ShopProducts`.`height`, `ShopProducts`.`depth`, `ShopProducts`.`manufacturer_id`, `ShopProducts`.`meta_description`, `ShopProducts`.`meta_keywords`, `ShopProducts`.`on_sale`, `ShopProducts`.`enabled`, `ShopProducts`.`track_inventory`, `ShopProducts`.`in_stock`, `ShopProducts`.`hide_if_out_of_stock`, `ShopProducts`.`stock_alert_threshold`, `ShopProducts`.`allow_pre_order`, `ShopProducts`.`created_user_id`, `ShopProducts`.`updated_user_id`, `ShopProducts`.`created_at`, `ShopProducts`.`updated_at`, `ShopProducts`.`page_id`, `ShopProducts`.`product_id`, `ShopProducts`.`grouped_option_desc`, `ShopProducts`.`grouped`, `ShopProducts`.`grouped_attribute_name`, `ShopProducts`.`tax_class_id`, `ShopProducts`.`product_type_id`, `ShopProducts`.`xml_data`, `ShopProducts`.`tier_prices_per_customer`, `ShopProducts`.`tier_price_compiled`, `ShopProducts`.`price_rules_compiled`, `ShopProducts`.`grouped_sort_order`, `ShopProducts`.`grouped_self_ghost_flag`, `ShopProducts`.`pt_description`, `ShopProducts`.`expected_availability_date`, `ShopProducts`.`enable_customer_group_filter`, `ShopProducts`.`disable_completely`, `ShopProducts`.`enable_perproduct_shipping_cost`, `ShopProducts`.`perproduct_shipping_cost`, `ShopProducts`.`price_rule_map_compiled`, `ShopProducts`.`cost`, `ShopProducts`.`title`, `ShopProducts`.`allow_negative_stock_values`, `ShopProducts`.`product_rating`, `ShopProducts`.`product_rating_all`, `ShopProducts`.`product_rating_review_num`, `ShopProducts`.`product_rating_all_review_num`, `ShopProducts`.`visibility_search`, `ShopProducts`.`visibility_catalog`, `ShopProducts`.`perproduct_shipping_cost_use_parent`, `ShopProducts`.`sale_price_or_discount`, `ShopProducts`.`price_index_compiled`, `ShopProducts`.`total_in_stock`, `ShopProducts`.`x_googleproducts_condition`, `ShopProducts`.`x_googleproducts_brand`, `ShopProducts`.`x_googleproducts_gtin`, `ShopProducts`.`x_googleproducts_mpn`, `ShopProducts`.`x_googleproducts_gender`, `ShopProducts`.`x_googleproducts_age_group`, `ShopProducts`.`x_googleproducts_color`, `ShopProducts`.`x_googleproducts_size`, `ShopProducts`.`x_googleproducts_included`, `ShopProducts`.`x_googleproducts_description`, `DbFiles`.`id` AS `DbFiles_id`, `DbFiles`.`mime_type` AS `DbFiles_mime_type`, `DbFiles`.`size` AS `DbFiles_size`, `DbFiles`.`description` AS `DbFiles_description`, `DbFiles`.`master_object_class` AS `DbFiles_master_object_class`, `DbFiles`.`master_object_id` AS `DbFiles_master_object_id`, `DbFiles`.`created_at` AS `DbFiles_created_at`, `DbFiles`.`created_user_id` AS `DbFiles_created_user_id`, `DbFiles`.`name` AS `DbFiles_name`, `DbFiles`.`disk_name` AS `DbFiles_disk_name`, `DbFiles`.`field` AS `DbFiles_field`, `DbFiles`.`is_public` AS `DbFiles_is_public`, `DbFiles`.`sort_order` AS `DbFiles_sort_order`, `DbFiles`.`title` AS `DbFiles_title` FROM `shop_products` AS `ShopProducts` LEFT JOIN `db_files` `DbFiles` ON (`ShopProducts`.`id` = `DbFiles`.`master_object_id`) WHERE `ShopProducts`.`description` IS NOT NULL ORDER BY rand() ASC LIMIT 4

Thanks for your help Bruno, slowly getting there. Now to figure out why xpdo isn't adding that one bit.... I'll have a good look tonight.

125 Posts

ficklelife Reply #9, 14 years, 1 month ago

I've modified the snippet and now I'm really confused. The snippet looks like this:


$query = $xpdo->newQuery('ShopProducts');
$query->select($xpdo->getSelectColumns('ShopProducts','ShopProducts',''));
// $query->leftJoin('DbFiles', 'DbFiles' );
$query->leftJoin('DbFiles','DbFiles','`ShopProducts`.`id` = `DbFiles`.`master_object_id`');
$query->select($xpdo->getSelectColumns('DbFiles','DbFiles','DbFiles_'));
$query->where(array(
    'ShopProducts.description:!=' => null
));
$query->sortby('rand()');
$query->limit(4);
 
 $query->prepare();echo '<pre>'.$query->toSql().'</pre>';


if ($collection = $xpdo->getCollection('ShopProducts',$query)){
    foreach ($collection as $object){
       $items[] = $object->toArray();

    }
    echo '<pre>' . print_r($items,1) .'</pre>';
}

It generated this SQL


SELECT `ShopProducts`.`id`, `ShopProducts`.`name`, `ShopProducts`.`description`, `ShopProducts`.`short_description`, `ShopProducts`.`url_name`, `ShopProducts`.`price`, `ShopProducts`.`sku`, `ShopProducts`.`weight`, `ShopProducts`.`width`, `ShopProducts`.`height`, `ShopProducts`.`depth`, `ShopProducts`.`manufacturer_id`, `ShopProducts`.`meta_description`, `ShopProducts`.`meta_keywords`, `ShopProducts`.`on_sale`, `ShopProducts`.`enabled`, `ShopProducts`.`track_inventory`, `ShopProducts`.`in_stock`, `ShopProducts`.`hide_if_out_of_stock`, `ShopProducts`.`stock_alert_threshold`, `ShopProducts`.`allow_pre_order`, `ShopProducts`.`created_user_id`, `ShopProducts`.`updated_user_id`, `ShopProducts`.`created_at`, `ShopProducts`.`updated_at`, `ShopProducts`.`page_id`, `ShopProducts`.`product_id`, `ShopProducts`.`grouped_option_desc`, `ShopProducts`.`grouped`, `ShopProducts`.`grouped_attribute_name`, `ShopProducts`.`tax_class_id`, `ShopProducts`.`product_type_id`, `ShopProducts`.`xml_data`, `ShopProducts`.`tier_prices_per_customer`, `ShopProducts`.`tier_price_compiled`, `ShopProducts`.`price_rules_compiled`, `ShopProducts`.`grouped_sort_order`, `ShopProducts`.`grouped_self_ghost_flag`, `ShopProducts`.`pt_description`, `ShopProducts`.`expected_availability_date`, `ShopProducts`.`enable_customer_group_filter`, `ShopProducts`.`disable_completely`, `ShopProducts`.`enable_perproduct_shipping_cost`, `ShopProducts`.`perproduct_shipping_cost`, `ShopProducts`.`price_rule_map_compiled`, `ShopProducts`.`cost`, `ShopProducts`.`title`, `ShopProducts`.`allow_negative_stock_values`, `ShopProducts`.`product_rating`, `ShopProducts`.`product_rating_all`, `ShopProducts`.`product_rating_review_num`, `ShopProducts`.`product_rating_all_review_num`, `ShopProducts`.`visibility_search`, `ShopProducts`.`visibility_catalog`, `ShopProducts`.`perproduct_shipping_cost_use_parent`, `ShopProducts`.`sale_price_or_discount`, `ShopProducts`.`price_index_compiled`, `ShopProducts`.`total_in_stock`, `ShopProducts`.`x_googleproducts_condition`, `ShopProducts`.`x_googleproducts_brand`, `ShopProducts`.`x_googleproducts_gtin`, `ShopProducts`.`x_googleproducts_mpn`, `ShopProducts`.`x_googleproducts_gender`, `ShopProducts`.`x_googleproducts_age_group`, `ShopProducts`.`x_googleproducts_color`, `ShopProducts`.`x_googleproducts_size`, `ShopProducts`.`x_googleproducts_included`, `ShopProducts`.`x_googleproducts_description`, `DbFiles`.`id` AS `DbFiles_id`, `DbFiles`.`mime_type` AS `DbFiles_mime_type`, `DbFiles`.`size` AS `DbFiles_size`, `DbFiles`.`description` AS `DbFiles_description`, `DbFiles`.`master_object_class` AS `DbFiles_master_object_class`, `DbFiles`.`master_object_id` AS `DbFiles_master_object_id`, `DbFiles`.`created_at` AS `DbFiles_created_at`, `DbFiles`.`created_user_id` AS `DbFiles_created_user_id`, `DbFiles`.`name` AS `DbFiles_name`, `DbFiles`.`disk_name` AS `DbFiles_disk_name`, `DbFiles`.`field` AS `DbFiles_field`, `DbFiles`.`is_public` AS `DbFiles_is_public`, `DbFiles`.`sort_order` AS `DbFiles_sort_order`, `DbFiles`.`title` AS `DbFiles_title` FROM `shop_products` AS `ShopProducts` LEFT JOIN `db_files` `DbFiles` ON `ShopProducts`.`id` = `DbFiles`.`master_object_id` WHERE `ShopProducts`.`description` IS NOT NULL ORDER BY rand() ASC LIMIT 4

Which executed fine as expected within phpmyadmin, joining the other table correctly. However, in the foreach loop that outputs the object values, the table isn't joined and only fields from ShopProducts are there, not DbFiles.

Any clues? Will play around a bit more...

- 125 Posts
ficklelife Reply #10, 14 years ago

Unfortunately still struggling with this. Is it because CMP generator doesn't generate schema for the "id" columns (which is the primary key) in each DB? Therefore modx can't join the tables as it can't "see" the id column, while running the raw SQL query is ok in phpmyadmin?