Jump to content


Replicated attribute in a Custom Table

sql mysql grocerycrud phpframework table querytable queryreturn renderquery render replicated

  • Please log in to reply
5 replies to this topic

#1 ded

ded

    Newbie

  • Members
  • Pip
  • 8 posts

Posted 09 April 2014 - 03:08 PM

Hey guys,

 

I've been using Grocery Crud to develop a simple local application that allows users to register themselves and like bands and rate them and select people they know that are also registered in the application.

 

Entities:
Person(person_id,person_URL, fullname, hometown)
Band(band_id,band_URL,band_name,country,genre)

 

Relationships:
Likes(person_id,band_id,rate)
Knows(person_id,known_person_id)

 

My questions are:

 

1) I want to return a table of person and known person like below:

KNOWS person_id | fullname | known_person_id | known_fullname

but I can't use *set_relation_n_n* function 'cause the relationship is (Person -> Likes -> Person), so it's giving me error. The other solution I came up with is making a custom table making a query to return the values I want and show it in the table (code below). The custom table returned is correct but when I render it to my Grocery Crud table, I need to specify $crud->columns('person_id', 'fullname', 'known_person_id', 'fullname'), and it cannot differentiate the fullname of the person and the fullname of the known person. How would I make it in order to be able to show the table that way?

 

2) I have the same issue in another table but could manage that using the function *set_relation_n_n* 'cause it's a relationship (Person -> Likes -> Band), so since it's 2 different entities it didn't return me a error. The problem is that the query (code below) returns me the whole table and I want only 25 records per page. When I try to use "LIMIT 25" in the query, it returns me ONLY 25 records and the "next page" button doesn't work. Any solutions?

Below, all the information:

 

 

CODE for question 1:

function knows_management()
{
$crud = new grocery_CRUD();
$crud->set_model('model_socialnetwork');
$crud->set_table('knows');
$crud->set_subject('Known');
$crud->basic_model->set_query_str('SELECT tb1.person_id, tb1.fullname, tb1.known_person_id, person.fullname FROM (SELECT person.person_id, person.fullname, knows.known_person_id FROM person INNER JOIN knows ON person.person_id = knows.person_id) tb1 INNER JOIN person ON tb1.known_person_id = person.person_id');<br>
$crud->columns('person_id','fullname','known_person_id','fullname');
$output = $crud->render();
$this->_socialnetwork_output($output);
}

 

 

CODE for question 2:

function likes_management()
{
$crud = new grocery_CRUD();
$crud->set_model('model_socialnetwork');
$crud->set_table('likes');
$crud->set_subject('Like');
$crud->columns('person_id','fullname','band_id','band_name', 'rate');
$crud->basic_model->set_query_str('SELECT tb2.person_id, tb2.fullname, tb2.band_id, band.band_name, tb2.rate FROM(SELECT tb1.person_id, person.fullname, tb1.band_id, tb1.rate FROM(SELECT person.person_id, likes.band_id, likes.rate FROM person INNER JOIN likes ON person.person_id = likes.person_id) tb1 INNER JOIN person ON tb1.person_id = person.person_id) tb2 INNER JOIN band ON tb2.band_id = band.band_id');
$output = $crud->render();
$this->_socialnetwork_output($output);
}



#2 ded

ded

    Newbie

  • Members
  • Pip
  • 8 posts

Posted 10 April 2014 - 01:26 PM

I'd be grateful for any help.



#3 Amit Shah

Amit Shah

    grocery CRUD Master

  • Advanced Member
  • PipPipPip
  • 1,192 posts
  • LocationMumbai, India

Posted 11 April 2014 - 03:08 AM

Well one thing - it would be easy for you to retrieve the data .. set 2 relationships to the 2 tables if u dont want to display the id and the name.. ID and the name is something need to think about but simply name can be set and retrieved using set_relation .. and change the display name to person / person know or whatever for the sake of display. 

 

Other way around if you want to have id and fullname both then i recommend u add 2 fake columns in the $crud->columns and set a callback on the columns. Using the callback will enable you to retrieve the values required which u can return / set.

 

if you go with setting your own query -> u are over riding the functionality of GC -> then u need to manage the pagination and stuff all on your own. U need to retrieve the page value / search filter value everything on yourselves and process the data correctly to get it rendered.

 

If you have super complex queries that cannot be set its output using single query single table (yes joins are acceptable) - then i will recommend you build your own code and display the records / data. Dont go for grocerycrud.



#4 ded

ded

    Newbie

  • Members
  • Pip
  • 8 posts

Posted 12 April 2014 - 05:57 PM

Thank you for your response Amit, using set_relation works like a charm.

 

Do you know if it is also possible to create other fields in the "add/insert" form in the person's table to feed another table?

 

For example:

 

So far I have in the person's insert fields:

Person URL:

Person Full Name:

Person Hometown:

 

and I would have:

Person URL:

Person Full Name:

Person Hometown:

Person Know:

Person Like:

 

The last two (Person Know and Person Like) would feed another table: Person Know would feed a column in my Knows Table and Person Like would feed a column in my Likes table.



#5 ded

ded

    Newbie

  • Members
  • Pip
  • 8 posts

Posted 12 April 2014 - 08:44 PM

I did some progress using Callback_after_insert but still I can't make it to work. In the function "fconhece", when I assign the values $primary_key to both variables "pessoa_id" and "pessoa_conhecida_id" it works, but when I try to assign another value to the variable "pessoa_conhecida_id" it doesn't work (like in the code below).
 
I'm putting a field in the add form that I don't have in my original table (the field is "pessoa_conhecida" and its value is going to the table "conhece").
 
public function test()
{
$crud = new grocery_CRUD();


$crud->set_table('pessoa');
$crud->set_subject('Pessoa');
$crud->add_fields('pessoa_link','nome_completo','cidade_natal','pessoa_conhecida');


$crud->callback_after_insert(array($this, 'fconhece'));


$output = $crud->render();


$this->_example_output($output);
}

 

function fconhece($post_array, $primary_key)
{
$user_logs_insert = array(
"pessoa_id" => $primary_key,
"pessoa_conhecida_id" => $post_array["pessoa_conhecida"]
     );


$this->db->insert('conhece',$user_logs_insert);


return true;
}

 



#6 ded

ded

    Newbie

  • Members
  • Pip
  • 8 posts

Posted 14 April 2014 - 04:36 AM

Well, what I managed to do is creating 10 columns in my original table (pessoa) and call it fake_conhece_pessoa_01, fake_conhece_pessoa_02,...
 
I'm not displaying these fields in the table, they just receive the values and with the callback_after_insert I send these values to another table.
 
I am also using a callback_before_insert to validate the fields fake_conhece_pessoa_XX ('cause I noticed that if these fields are left blank and the user submit the add form, the form actually doesn't send the values to the table and the page stays stuck at the form). so the callback_before_insert checks if the field is empty and if it is it just puts the value 0 (zero) to it.
 
The problem is that I don't know if the problem is that this "check" function is not working 'cause of some bad coding or if it has something to do with the set_relation that I have on every fake_conhece_pessoa_XX. Does the set_relation kind of make the user obligated to put value in it? Or if the field is with set_relation I can't add value to it if it is left blank?
 
function pessoa_management()
{
$crud = new grocery_CRUD();


$crud->set_table('pessoa');
$crud->set_subject('Pessoa');
$crud->columns('pessoa_link','nome_completo','cidade_natal');
$crud->fields('pessoa_link','nome_completo','cidade_natal');
$crud->required_fields('pessoa_link','nome_completo','cidade_natal');
$crud->display_as('pessoa_id','Pessoa ID')
->display_as('pessoa_link','Pessoa URL')
->display_as('nome_completo','Nome Completo')
->display_as('cidade_natal','Cidade Natal');
->display_as('fake_conhece_pessoa_01','Pessoa Conhecida')
->display_as('fake_conhece_pessoa_02','Pessoa Conhecida')
->display_as('fake_conhece_pessoa_03','Pessoa Conhecida')
->display_as('fake_conhece_pessoa_04','Pessoa Conhecida')
->display_as('fake_conhece_pessoa_05','Pessoa Conhecida')
->display_as('fake_conhece_pessoa_06','Pessoa Conhecida')
->display_as('fake_conhece_pessoa_07','Pessoa Conhecida')
->display_as('fake_conhece_pessoa_08','Pessoa Conhecida')
->display_as('fake_conhece_pessoa_09','Pessoa Conhecida')
->display_as('fake_conhece_pessoa_10','Pessoa Conhecida');


$crud->set_relation('fake_conhece_pessoa_01','pessoa','nome_completo');
$crud->set_relation('fake_conhece_pessoa_02','pessoa','nome_completo');
$crud->set_relation('fake_conhece_pessoa_03','pessoa','nome_completo');
$crud->set_relation('fake_conhece_pessoa_04','pessoa','nome_completo');
$crud->set_relation('fake_conhece_pessoa_05','pessoa','nome_completo');
$crud->set_relation('fake_conhece_pessoa_06','pessoa','nome_completo');
$crud->set_relation('fake_conhece_pessoa_07','pessoa','nome_completo');
$crud->set_relation('fake_conhece_pessoa_08','pessoa','nome_completo');
$crud->set_relation('fake_conhece_pessoa_09','pessoa','nome_completo');
$crud->set_relation('fake_conhece_pessoa_10','pessoa','nome_completo');


$crud->callback_after_insert(array($this, 'fconhece'));
$crud->callback_before_insert(array($this,'check'));
$crud->callback_before_update(array($this,'check'));


$output = $crud->render();


$this->_redesocial_output($output);
}

 

function check($post_array)
{
if(empty($post_array['fake_conhece_pessoa_01'])) {
$post_array['fake_conhece_pessoa_01'] = 0;
}


if(empty($post_array['fake_conhece_pessoa_02'])) {
$post_array['fake_conhece_pessoa_02'] = 0;
}


if(empty($post_array['fake_conhece_pessoa_03'])) {
$post_array['fake_conhece_pessoa_03'] = 0;
}


...
 
 
return $post_array;
}
function fconhece($post_array, $primary_key)
{
$user_logs_insert01 = array("pessoa_id" => $primary_key, "pessoa_conhecida_id" => $post_array['fake_conhece_pessoa_01']);
$user_logs_insert02 = array("pessoa_id" => $primary_key, "pessoa_conhecida_id" => $post_array['fake_conhece_pessoa_02']);
$user_logs_insert03 = array("pessoa_id" => $primary_key, "pessoa_conhecida_id" => $post_array['fake_conhece_pessoa_03']);
$user_logs_insert04 = array("pessoa_id" => $primary_key, "pessoa_conhecida_id" => $post_array['fake_conhece_pessoa_04']);
$user_logs_insert05 = array("pessoa_id" => $primary_key, "pessoa_conhecida_id" => $post_array['fake_conhece_pessoa_05']);
$user_logs_insert06 = array("pessoa_id" => $primary_key, "pessoa_conhecida_id" => $post_array['fake_conhece_pessoa_06']);
$user_logs_insert07 = array("pessoa_id" => $primary_key, "pessoa_conhecida_id" => $post_array['fake_conhece_pessoa_07']);
$user_logs_insert08 = array("pessoa_id" => $primary_key, "pessoa_conhecida_id" => $post_array['fake_conhece_pessoa_08']);
$user_logs_insert09 = array("pessoa_id" => $primary_key, "pessoa_conhecida_id" => $post_array['fake_conhece_pessoa_09']);
$user_logs_insert10 = array("pessoa_id" => $primary_key, "pessoa_conhecida_id" => $post_array['fake_conhece_pessoa_10']);
 
$this->db->insert('conhece',$user_logs_insert01);
$this->db->insert('conhece',$user_logs_insert02);
$this->db->insert('conhece',$user_logs_insert03);
$this->db->insert('conhece',$user_logs_insert04);
$this->db->insert('conhece',$user_logs_insert05);
$this->db->insert('conhece',$user_logs_insert06);
$this->db->insert('conhece',$user_logs_insert07);
$this->db->insert('conhece',$user_logs_insert08);
$this->db->insert('conhece',$user_logs_insert09);
$this->db->insert('conhece',$user_logs_insert10);


return true;
}






Also tagged with one or more of these keywords: sql, mysql, grocerycrud, phpframework, table, querytable, queryreturn, renderquery, render, replicated

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users