[bvarlb]

Hello,

 

I use :

$crud->set_relation('id_circuit','circuits','nom_circuit');
with where clause on the same field :
$crud->where('id_circuit', $id_circuit);

I catch an error :

       A Database error occurred
       Error Number: 1052
        Champ: 'id_circuit' in where clause is ambiguous
        SELECT `sorties`.*, j4f75133e.nom_circuit AS s4f75133e FROM (`sorties`) LEFT JOIN `circuits` as j4f75133e ON `j4f75133e`.`id_circuit` = `sorties`.`id_circuit` WHERE `id_circuit` = '3' ORDER BY `date_sortie` desc LIMIT 20
        Filename: C:\Data\web\applis\grocerycrud\system\database\DB_driver.php
        Line Number: 330

 

If I prefix with the used alias I can see when I debug, it works :

$crud->where('j4f75133e.id_circuit', $id_circuit);

 

but it works only the first time and pagination is not working, I can see the query is :

       SELECT * FROM (`sorties`) LEFT JOIN `circuits` as j4f75133e ON `j4f75133e`.`id_circuit` = `sorties`.`id_circuit`
       WHERE `j4f75133e`.`id_circuit` = 'ajax_list'

 

My questions are :

- Do you know if the aliases names are always the same ?

- Where comes from 'ajax_list' ?

- Have I to use the set_model function to do what I want in a custom model ?

 

Definition of tables :

CREATE TABLE IF NOT EXISTS `circuits` (
        id_circuit INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY
    ,    nom_circuit VARCHAR(128) NOT NULL
    ,    total_kms DECIMAL(4,1) NOT NULL
    ,    total_deniv INT(4) NOT NULL
  , lien VARCHAR(255)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `sorties` (
        id_sortie INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY
    , id_circuit INT(10) NOT NULL
    ,    date_sortie DATE NOT NULL
    ,    kms_sortie INT(3) NOT NULL
    ,    temps_sortie TIME NOT NULL
    ,    comments_sortie TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

In the controller, I have this code :

              $crud = new grocery_CRUD();
              $crud->set_subject('Sortie');
              $crud->set_table('sorties');
              $crud->where('j4f75133e.id_circuit', $id_circuit);
              $crud->order_by('date_sortie', 'desc');
              $crud->set_relation('id_circuit','circuits','nom_circuit');
 

[davidoster]

You need to prepend the id_circuit with the name of the table you use withing your set_table function at the where clause.

So it will be like this:  $crud->where('sorties.id_circuit', $id_circuit);

[bvarlb]

Thank you very much for your help

but function works and display what I want only if there is not more than 1 page to display,

there seems to be a first stealth display replaced by an empty list

if 1 page to display I see the list but pagination don't work (if I have 1 page and press on next, it displays no list, after previous button displays no list, refresh (F5) not better)

Have you an idea ?

 

Thanks in advance

[davidoster]

This line of code you have is wrong, $crud->where('j4f75133e.id_circuit', $id_circuit);

If you replace it with what I proposed before you won't have any more problems.

[bvarlb]

sorry but I have replaced the code as you said :

$crud->where('j4f75133e.id_circuit', $id_circuit);

replaced by

$crud->where('sorties.id_circuit', $id_circuit);

[davidoster]

ooooo! interesting!!!

which version do you use?

 

the only time, that I am aware of that we need to make some internal modification (within our controller) is when we use set_relation and callback on the same field.

 

this shouldn't happen to be honest!

The only thing I can think of is to put also on the set_relation the table.field_name , e.g. $crud->set_relation('sorties.id_circuit','circuits','nom_circuit');

 

try this also.

[bvarlb]

I use last version 1.3.3, with the code you suggest :
$crud->set_relation('sorties.id_circuit','circuits','nom_circuit');
I catch this error :

A Database Error Occurred

Error Number: 1054

Champ 'sorties.sorties.id_circuit' inconnu dans on clause

SELECT `sorties`.*, j9fe111e0.nom_circuit AS s9fe111e0 FROM (`sorties`) LEFT JOIN `circuits` as j9fe111e0 ON `j9fe111e0`.`id_circuit` = `sorties`.`sorties`.`id_circuit` WHERE `sorties`.`id_circuit` = '3' ORDER BY `date_sortie` desc LIMIT 20

Filename: C:\Data\web\applis\grocerycrud\system\database\DB_driver.php

Line Number: 330

I think the problem is the use of where with the relation.
without this line : $crud->where('sorties.id_circuit', $id_circuit);
the link from "Parcours List" works (all lines of sorties table are displayed and we can sort, search and paginate as we want).

However I have another function in the same controller to display all sorties for a selected month/year. It works fine with this code :
$crud->set_table('sorties');
$crud->where('year(date_sortie)', $annee);
$crud->where('month(date_sortie)', $mois);
$crud->order_by('date_sortie', 'desc');
$crud->set_relation('id_circuit','circuits','nom_circuit');

[victor]

Use unical names and have no problems.

I use unical prefixex for each column.

[bvarlb]

Hi Victor,
you're right I should have renamed fields to have unique names between all tables. It's what I've done.
But after this action I have the same behaviour : A first list is displayed with the good results and displayed immediately after with an empty list.

The query I can see with debug is good :
SELECT *
FROM (`sorties`)
LEFT JOIN `circuits` as j66aa657d ON `j66aa657d`.`id_circuit` = `sorties`.`id_circuit_sortie`
WHERE `id_circuit_sortie` = '3'

There is another problem I can't see now. I'm going to debug...
 

Thanks for all

[Christophe Conduché]

Hi Victor !

 

 

I have the same problem with a basic relation n_n and in this case according to the documentation, the names of the columns HAVE to be the same in the master table, the relation table and the slave table.....

 

 

 

 

Error Number: 1052

Column 'journee_id' in where clause is ambiguous

SELECT *, billet as se9d339b1
FROM (`journee_pax`)
JOIN `pax` ON `journee_pax`.`pax_id` = `pax`.`pax_id`
WHERE `journee_id` = '6'
ORDER BY `journee_pax`.`priority`

Filename: /var/www/gestpara/models/grocery_crud_model.php

Line Number: 333

[Christophe Conduché]

oopps,  sorry, in my case, I had a copy of the column journee_id in the table pax... i renamed it and now it works fine.

 

all this as we have to "fight" to implement 1:N  while N:1 and N:N only are allowed by GC !

[victor]

bvarlb,I think you get empty list because you use the 'where' function. flexigrid send request to server to get sorting after loading. and maybe your $annee is empty. use uri segments instead ?date=...

[bvarlb]

Hi Victor,

 

I confirm I use flexigrid, I understand when you say "flexigrid send request to server to get sorting after loading"

I've tried your suggestion by replacing :

$crud->where('id_circuit_sortie', $id_circuit);

by :

$crud->where('id_circuit_sortie', $this->uri->segment(3));

and it works fine now.

 

Thank you very much for your help