[Racing Chocobo]

Hi all, may I ask one question about grocerycrud models?

My question is below:

I want to list some data that need to join some tables to retrive the data, so I created my models (named 'goods_models') and load it in the controller, something like this:

function show()
{
$crud = new grocery_CRUD();
$crud->set_model('goods_model');
$crud->set_theme('datatables');
$crud->set_table('goods');

$crud->columns('id', 'type_code', 'item_type_code', 'preview_url', 'file_url_android', 'file_url_ios', 'name', 'title', 'content', 'downloads', 'hot_value', 'time_beg', 'time_end');
$crud->add_fields('id', 'type_code', 'item_type_code', 'preview_url', 'file_url_android', 'file_url_ios', 'name', 'title', 'content', 'downloads', 'hot_value');
$crud->edit_fields('id', 'type_code', 'item_type_code', 'preview_url', 'file_url_android', 'file_url_ios', 'name', 'title', 'content', 'downloads', 'hot_value');

$crud->set_subject('goods');
            
$output = $crud->render();
$this->load->view('show.php',$output);   
}

and my 'goods_model' :

function get_list() {

if($this->table_name === null) {
return false;
}

$select = "{$this->table_name}.*";
$_select = array(
'goods.id', 'goods.type_code', 'goods.item_type_code', 'goods.preview_url, goods.file_url_android, goods.file_url_ios, cooperation.name, good_text.title, good_text.content, good_shopping.downloads, good_shopping.hot_value, good_shopping.time_beg, good_shopping.time_end'
);
$select .= ','. implode(',', $_select);
if( ! empty($this->relation))
foreach ($this->relation as $relation) {
list($field_name , $related_table , $related_field_title) = $relation;

$unique_join_name = $this->_unique_join_name($field_name);
$unique_field_name = $this->_unique_field_name($field_name);

if (strstr($related_field_title,'{')) {
$select .= ", CONCAT('".str_replace(array('{','}'),array("',COALESCE({$unique_join_name}.",", ''),'"),str_replace("'","\\'",$related_field_title))."') as $unique_field_name";
}
else {
$select .= ", $unique_join_name.$related_field_title as $unique_field_name";
}

if ($this->field_exists($related_field_title)) {
$select .= ", {$this->table_name}.$related_field_title as '{$this->table_name}.$related_field_title'";
}
}

$this->db->select($select, false);

$this->db->join('good_coop','good_coop.good_sn = goods.sn')
->join('cooperation','cooperation.sn = good_coop.coop_sn')
->join('good_shopping','good_shopping.good_sn = goods.sn')
->join('good_text','good_text.good_sn = goods.sn');

$results = $this->db->get($this->table_name)->result();

return $results;
}

For the list data, it's work very well and can show all of the data from 5 join table (good, good_coop, cooperation, good_shopping, good_text).

However, since in the controller, we only can set one table, and I set it into 'goods' ($crud->set_table('goods') ;), I always get this error result:

 

Error Number: 1054

Unknown column 'name' in 'field list'

INSERT INTO `goods` (`id`, `type_code`, `item_type_code`, `preview_url`, `file_url_android`, `file_url_ios`, `name`, `title`, `content`, `downloads`, `hot_value`) VALUES ('33', '2', '2', '33', '33', '33', '33', '33', '33', '33', '33')

 

'

Which means it just insert to 'goods' table instead of the specific 4 others table that I've joined in the 'goods_model'.

Any solution for this problem?

Thanks before!

Thank you~

[davidoster]

Hello and welcome to the forums [member=racing chocobo].

Well I haven't check the whole of your code but on your select,

 

$_select = array(
'goods.id', 'goods.type_code', 'goods.item_type_code', 'goods.preview_url, goods.file_url_android, goods.file_url_ios, cooperation.name, good_text.title, good_text.content, good_shopping.downloads, good_shopping.hot_value, good_shopping.time_beg, good_shopping.time_end'
);

you have cooperation.name

Probably this is why it can't get it?

You need to define on your

$crud->columns

         ->add_fields

         ->edit_fields

the names of your joint tables also.

Check it and let us know.

 

P.S. if the 'id' field is your primary key why do you include it on add/edit_fields? This is an autonumber field!

[Racing Chocobo]

Hi davidoster,

Thank you very much for the reply.

 

Hm, the cooperation.name you mention should not have any problem, since I also joined the table 'cooperation', in the join command (as below):

$this->db->join('good_coop','good_coop.good_sn = goods.sn')
->join('cooperation','cooperation.sn = good_coop.coop_sn')
->join('good_shopping','good_shopping.good_sn = goods.sn')
->join('good_text','good_text.good_sn = goods.sn');

and I also need to fetch the cooperation name for the listing, and it work perfectly.

I think the most problem is I still donno how to insert data to multiple join table correspondingly.

[Racing Chocobo]

Hi Davidoster,

 

Yeah actually you mention one of the point that related with my question.

 

$crud->columns

         ->add_fields

         ->edit_fields

the names of your joint tables also.

 

You say that I need to names the joint tables name too, this is also what I'm trying to do I think what should be do.

However, did we can only set one table for each time?

so I can only set the table as 'goods' by using  "$crud->set_table('goods');"

but can't set to the others.

May you tell me how to set the names of tables specificly inside the controller function?

Thank you very much again~

[davidoster]

Because you have a different get_list function you need to define the table's name for each field as usual, table_name.field,

e.g.

 

$crud->columns('goods.id',...

 

check if this solves the problem.

[Racing Chocobo]

I've tried that one after you mention it before, but it even can't work for the data listing, it return empty if I change the id --> goods.id, type_code --> goods.type_code, etc
I tried to change the columns, add_fields, edit_fields all of them but it still can't work and I get another error while try to insert with this settings:

goods.id', 'goods.type_code', 'goods.item_type_code', 'goods.preview_url', 'goods.file_url_android', 'goods.file_url_ios', 'cooperation.name', 'good_text.title', 'good_text.content', 'good_shopping.downloads', 'good_shopping.hot_value

$crud->add_fields('goods.id', 'goods.type_code', 'goods.item_type_code', 'goods.preview_url', 'goods.file_url_android', 'goods.file_url_ios', 'cooperation.name', ....

the error message while insert :

 

 

A Database Error Occurred

You must use the "set" method to update an entry.

Filename: C:\xampp\htdocs\cardQ\system\database\DB_active_rec.php

Line Number: 1174

 

 

 

Thank you~

[davidoster]

Let's rewind a bit.

What is it exactly that you're trying to do here?

Can you show the structure of the tables and what kind of relations you want to achieve?

[Racing Chocobo]

Hi Davidoster,

 

This is what I'm trying to do:

I have a table goods, good_coop, good_shopping, good_text, and cooperation (as the picture below)

 

[attachment=621:diagram.PNG]

 

 

All what I want to do is get all of the attribute that have the same id of good_sn (goods serial number) show it, and also let the user can add or edit the data

The result is something like this:

 

[attachment=622:show.PNG]

 

However I encounter some problem for inserting the data, since I can only set the table once ($crud->set_table('goods') ;) and I set it into table 'goods', so everytime I insert to the database, I will add all the things into that table and occur error (since some of the fields should insert into another table instead of 'goods')

You can see the error message as below: (seems that it insert all of the fields only into 'goods' table, and the 'name', 'title', 'content', 'downloads', 'hot_value' should not insert into 'goods' but into another corresponding field table)

 

 

Error Number: 1054

Unknown column 'name' in 'field list'

INSERT INTO `goods` (`id`, `type_code`, `item_type_code`, `preview_url`, `file_url_android`, `file_url_ios`, `name`, `title`, `content`, `downloads`, `hot_value`) VALUES ('2', '2', '2', 'http://cdn0.sbnation.com/entry_photo_images/8101001/smartphone-lineup_1020_large_verge_medium_landscape.jpg' 'http://cdn0.sbnation.com/entry_photo_images/8101001/smartphone-lineup_1020_large_verge_medium_landscape.jpg' 'http://cdn0.sbnation.com/entry_photo_images/8101001/smartphone-lineup_1020_large_verge_medium_landscape.jpg' 'dd', 'dd', 'dd', '3', 'd')

 

 

 

Thank you very much

[davidoster]

Right. Ok correct me if I don't get it right.

tha main table is goods and the rest of the tables are defining the good (a product) itself, in terms of :

- when this product was shopped (good_shopping),  

- its type (good_type) and title (good_text) and

- something that is called cooperation that I don't understand what it is but it doesn't matter

 

Now, please correct me if I am wrong,

aren't most of  these tables (if not all), except the goods table of course, having additional information that needs to be used while we are adding a new good (product)?

If this is tha case, that the rest of the tables just define properties of the main goods, then :

1. You don't need to alter the model that Grocery CRUD is using,

2. You can use the set_relation and/or set_relation_n_n functions to make these "joins" via relations

3. in cases that the set_relations aren't enough you can make a normal model (not an one that overrides the Grocery CRUD's model) and make your own custom relations there, you can see this way here

[Racing Chocobo]

Thank you very much again for the reply.

I've tried the method you tell me for the set_relation. However it only can work for several tables, but not for all of the table.

the set_relation can only work for the table 'goods' and 'good_type', since the field 'type_code' is correctly related to 'code' in 'good_type', so I can get the 'name' in field 'good_type'

 

However, for the other table, let make it simple, just take a look at 'goods' and 'good_shopping' these two tables.

How can I make this sql command "SELECT sh.downloads FROM `goods` as g LEFT JOIN `good_shopping` as sh ON g.sn = sh.good_sn" by using set_relation?

I think we can't right? since the table we set to the crud is 'goods' instead of 'good_shopping'

 

And another question about the relation, may this will work while we add a new data, isn't it will be work only for the display but still will not work for the insert/update?

for example can it auto discover which table the data should be insert?

 

Terribly sorry for my annoying question, but I really eager to figure what is the best method for add to multiple tables instead of using callback function.

Thank you

[Racing Chocobo]

Let me simplify my question, what I'm trying to do is the database inserting and editing.

Change the database structure into this one:

[attachment=623:db.PNG]

 

How to do insert when in one time for these two tables?

While the insert input is like this:

 

[attachment=624:in.PNG]

 

the insert list above is from the two tables, 'id' and 'active_status' are from 'Item' table and

'name' and 'value' are from 'Item_Description'.

 

In other words, how to let the crud auto detect which table to insert for each data?

id, active_status insert to --> 'Item' table

and

name, value insert to --> 'Item_Description'  (according to the item id insert above)

 

I can do manually these by pure php, but I'm pretty sure that grocery crud should can help me to do this.

Thank you.

[davidoster]

Ok let me answer your last question first.

The set_relation function is a way to bring data from another table to the one we are.

So in your example the set_relation function will NOT work because it doesn't auto insert values to the related table!

The set_relation function just fetches data from the corresponding field(s) to the main table.

So what you describe here is not doable.

 

You need to have some items inserted and then go to the Item_description :

- to select the item (via a set_relation)

- fill in the rest of the data

 

Basically what you describe is a Master/Detail structure that is not currently supported at version 1.3.3 of Grocery CRUD.

[Racing Chocobo]

I understand that set_relation is used only for fetching the data, so I didn't use that and write my own models.

 

Ok I got it, so I still need to do it separately for inserting to more than one table at one time?

I know how to to that by posting method, manually separate all of the fields from the post data,

but I think this method is too inconvenient that let me curious whether there is better method.

 

Since you say that there isn't any method supported at current version, so I'll do it manually using php function for it.

However thank you very much for the explain.

[davidoster]

The only option is to use callback_before/after_insert and via the callback function store the data to the other table.

[Racing Chocobo]

Yeah I know how to do with the callback function.

Thank you very very much!