Forums

Hi Bob,

I am trying to run the following sql in a DB multi record loader:

SELECT 
  cs_content.id,
  cs_content.title,
  cs_categories.title
FROM
  cs_content
  INNER JOIN cs_categories ON (cs_content.catid = cs_categories.id)

but I keep getting the follwing error message:

JDatabaseMySQLi::query: 1052 - Column 'id' in field list is ambiguous SQL=SELECT `id`, `title`, `catid`, `cat`.`title` AS `cat.title` FROM `cs_content` AS `art` INNER JOIN `cs_categories` AS `cat` ON art.catid=cat.id

The issue is that field id is not prepended with the Model ID and same issue with title and catid.
If I list the fields like 'art'.'id','art'.'title', etc. I get another error message stating

Unknown column 'art.id' in 'field list'

So how can I solve the issue above?

thanks,
adop

Hi adop,

I struggle a bit to understand the DB Multi_Record Loader with JOINs - they seem to work if you get everything just right. What settings do you have?

Bob

PS if you add, for example, `art`.`id` with quotes then you must use back-ticks `` not straight quotes ''

Bob

Hi Bob,

I use CF V4 wth the following settings:
Content table
DB field: blank
Table: cs_content
Request param: blank
Model ID: art
Fields: id,title,catid
Load data: yes
Data load type: all
Enable association: yes
Join type: inner
Join rule:art.catid=cat.id
Associated models: cat
Group model data: yes

Categories table
DB field: blank
Table: cs_categories
Request param: blank
Model ID: cat
Fields: title
Load data: no
Data load type: all
Enable association: no
Join type: inner
Join rule: blank
Associated models: blank
Group model data: yes

I have also played with back-ticks but they are not welcomed. They always trigger some error.

Thanks for your help.

Regards,
adop

Hi adop,

This is a messy workaround but using art`.`id,art`.`title,art`.`catid in the fields list at least gives a valid MySQL query that returns the correct data.

Bob

Hi Bob,

I have used the workaround that you suggest and actually it originates a correct sql string:

SELECT `art`.`id`, `art`.`title`, `cat`.`title` AS `cat.title` FROM `cs_content` AS `art` INNER JOIN `cs_categories` AS `cat` ON (cat.id=art.catid) 

I have tested it and it's correct. However there are still a couple of issues.
Firstly I get empty records displayed accompanied by the following error message:

Notice: Undefined index: `cat`.`title` in .....administrator\components\com_chronoforms\form_actions\db_multi_record_loader\cfaction_db_multi_record_loader.php on line 77

I get this message for each record extracted and for each column processed. Line 77 btw is:

$output .= '<td class="col'.($k+1).' cell">'.$record[$field_name].'</td>';

$field_name is "`art`.`id`",or "`art`.`title`", or "`cat`.`title`", ie the column name is always prepended with the model id. On the other hand the array of data extracted is structured like this:

[1] => Array
(
[id] => 71
[title] => Work with us
[cat] => Array
(
[title] => General - en-GB
)

ie no model id in front of the column names and consequently the error message is triggered.

Secondly even fixing the issue above by exploding $field_name and removing the back tick still leaves an issue associated with the fact that in the returned array the third field [cat], associated with model id cat, is an array instead of a string. So applying the fix above produces two columns with the same name [title].
Any idea how to go about it?

Thanks,
adop