⚠ In case you've missed it, we have migrated to our new website, with a brand new forum. For more details about the migration you can read our blog post for website migration. This is an archived forum. ⚠

  •     

profile picture

Database error with field used in relation and in where clause



bvarlb

bvarlb
  • profile picture
  • Member

Posted 17 April 2013 - 08:48 AM

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

davidoster
  • profile picture
  • Member

Posted 18 April 2013 - 06:41 AM

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

bvarlb
  • profile picture
  • Member

Posted 18 April 2013 - 08:42 AM

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

davidoster
  • profile picture
  • Member

Posted 18 April 2013 - 08:52 AM

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

bvarlb
  • profile picture
  • Member

Posted 18 April 2013 - 09:30 AM

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

davidoster
  • profile picture
  • Member

Posted 18 April 2013 - 09:57 AM

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

bvarlb
  • profile picture
  • Member

Posted 18 April 2013 - 14:43 PM

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

victor
  • profile picture
  • Member

Posted 18 April 2013 - 18:55 PM

Use unical names and have no problems.

I use unical prefixex for each column.


bvarlb

bvarlb
  • profile picture
  • Member

Posted 19 April 2013 - 08:37 AM

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é

Christophe Conduché
  • profile picture
  • Member

Posted 19 April 2013 - 09:19 AM

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é

Christophe Conduché
  • profile picture
  • Member

Posted 19 April 2013 - 09:28 AM

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

victor
  • profile picture
  • Member

Posted 19 April 2013 - 11:34 AM

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

bvarlb
  • profile picture
  • Member

Posted 19 April 2013 - 13:01 PM

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