[jpeake]

First a huge thank you for your work and willingness to share it. Like others I think this is awesome!!! I'm fairly new to Code igniter and Grocery CRUD, so I hope I ask this question in a way that makes sense.

I have a table that stores files and the files are catagorized using a relation_n_n.
The base table is named 'files' where all of the files are stored.
The selection table is name 'category' and has the different categories that files are identified with.
The relation table is name 'file_category' where file_id, category_id, and priority are all stored.

I am trying to create and display a view of a table that shows all of the files that are in a certain category. For example I have 1000 files, 200 of them are marked as 'short stories' I would like to create a CRUD table that displays only the files that are 'short stories', but I am having trouble because my $crud_like looks like this:
$crud->like('category','short stories');
So, of course I get 'unknown column' because the column 'category' only exist in the selection table which is not part of my base table 'files'.

I have been able to get the category column to show up in my files list table by using:
$crud->set_relation_n_n('category', 'file_category', 'category', 'file_id', 'category_id', 'name', 'priority');
Unfortionatly I still can't use $crud->like('category','short stories');

I created a model and tried writing a query to get the data... this query does what I want, but I can't figure out how to implement it in a CRUD list table.
$query = $this->db->query("SELECT * FROM files INNER JOIN file_category ON files.file_id=file_category.file_id WHERE category_id = (SELECT category_id FROM category WHERE name = 'short stories') ORDER BY files.file_id DESC") or die(mysql_error());

So, I'm stuck, can anyone tell me if it is possible to show results from a base table that meet a criteria that is in a selection table?

[noskov.biz]

Hi!

Is it really necessary for you to use n to n relation? Why do you not try grocery_CRUD 1 to n relation in which you can use where clause for the 4-th parameter? Sorry, if I asked a stupid question Sometimes it's difficult to understand at once all details.

[jpeake]

Hey there, thanks for the quick response... and your other responses, I have learned a lot from you the last several weeks.

I think you are correct, it could be a 1_n relationship. I tried to rewrite my function to do this:
$crud->set_table('files');
$crud->set_relation('file_id','file_category','category_id',array('category_id' => '42'),'priority ASC');

file_id is my primary key in my base table
file_category is my relation table
category_id is my related title field
42 is the primary key for 'short story' in the selection table

This didn't work so then I thought I would try to set $crud->set_table('file_category') that is my relationship table. I tried this
$crud->set_table('file_category');
$crud->set_relation('file_id','files', '{file_id} - {file_title} - {file_path}',array('category_id' => '42'),'priority ASC');

I can't get that to work either But, I'm pretty tired and don't think so well this late at night. I am going to get some sleep and give it another try tomorrow. Thanks for your direction.

[noskov.biz]

Well, let's say we have 2 tables: [b]files[/b] and [b]categories[/b]. In files table we have: [b]file_id[/b], [b]file_name[/b] and [b]file_category[/b] fields. In categories table we have: [b]category_id[/b] and [b]category_name[/b] fields. So if you want just to show files that are applied only to the specific category you can write something like this (look at the comments below):

public function files()

{

$crud = new grocery_CRUD();

$crud->set_table('files');

$crud->set_subject('Files');



$cat = $this->db->get_where('categories', array('category_id' => '1'))->row();



$crud->like('file_category', $cat->category_id);

// or if you want to use where clause

// $crud->where('file_category', $cat->category_id);



// if you want that user CAN change the category of files

$crud->set_relation('file_category', 'categories', 'category_name');



// if you want that user CAN NOT change the category of files

// $crud->set_relation('file_category', 'categories', 'category_name', array('category_id' => $cat->category_id));



// if you want to hide the category at all,

// so user CAN NOT see it on add/edit operations & CAN NOT change it

// $crud->set_relation('file_category', 'categories', 'category_name');

// $crud->change_field_type('file_category', 'hidden', $cat->category_id);



$output = $crud->render();

$this->_example_output($output);

}

[jpeake]

Ok, I made a little progress, but am stuck again. I can get the list table to select and show the files that I want.

$crud->set_table('file_category');
$crud->set_relation('file_id','files', '{file_title}');
$crud->like('category_id','21');

My next problem, which I think is connected to the way I did the above is that I want to do 'add_action' and give the user a hyperlink in the list table to download the files.

$crud->add_action('Download', '../img/icons/16x16/download_img_blue.png', '','ui-icon-image',array($this,'download_file'));
function download_file($primary_key , $row)
{
return site_url('/uploads/files')."/". {file_path};
}

I know that "return site_url('/uploads/files')."/". {file_path};" is wrong and that {file_path} is not available in the function, but I need file_path from the files table and I am not sure how to get it in that function.

Any direction appreciated... thanks.

[noskov.biz]

Your 'download_file' callback function takes 2 parametres. In the second parameter [b]$row[/b] you have data in [b]object[/b] that you can use for creating file download url which will be based on the name of you file. For example:

function download_file($primary_key, $row)

{

  return site_url('uploads/files/' . $row->file_name . '.pdf');

}

[jpeake]

Just wanted to write a quick note of thanks, I had to struggle with this a bit more, in my last post I had actually tried to query the reference table which turned out to be a mistake because it made it impossible to to use $row->file_name the way you suggested. But after following your direction and studying on it a bit I was able to get it to work. I'm not sure it is best practices quality, but it might help someone else who is trying to learn so I posted my code below. Thanks.

//$single_category_id is set by using a model to query the category table so if the user request the "short stories" page I first use a model to get the category_id from the category table. In this case "short stories" = '21'

$crud->set_table('files');
$crud->set_relation('file_id','file_category', 'file_id',array('category_id' => $single_category_id),'priority ASC');
$crud->like('category_id', $single_category_id);

As I mentioned above, I'm new and this probably isn't best practice, but it works. Thanks for your help.

[noskov.biz]

You are always welcome!

[jpeake]

Uhhhhgggg... I'm back again, I was wrong, I thought I had it working by using some queries to help me get the files I needed, but it turns out that if I do that then the "search" feature at the bottom of the table cannot work correctly. I am back to thinking that I have to do a set_relation_n_n and somehow incorporate a "WHERE" statement in it. I don't think I have explained my situation very well so I put together a graphic to show the situation that I am dealing with. I think I am now going to throw myself on the mercy of some Grocery CRUD experts and ask for help. I have read and worked on this for several days and cannot figure it out. As always, any help is greatly appreciated.
[img]http://www.bytheowl.com/georgia/files_question.jpg[/img]

[noskov.biz]

Please, tell me your are working with [b]new[/b] project or you [b]redesign[/b] the project which already exists?

[jpeake]

This is a new project, my first with Code Igniter and Grocery Crud. I am at the very start of this project.

[noskov.biz]

Well, if I understand correctly you have plenty of files, but I do not understand how do you sort and categorize it? Will you have hardcoded categories or you want make them dinamically? Then how user will upload files and how they will be organized in folders at the server?

P.S. Why the search button doesn't work well with 1 to n relation? What errors have you got?

[jpeake]

How I categorize files:

I have a function below named 'function manage_files' that allows site administrators to upload files, when they click the 'add' or 'edit' buttons they have the option to put the files in one category, or they can put the file in multiple categories. I also wanted to allow the site administrators to add another category if they needed to, so if they needed a new category they could go to a different grocery CRUD table to add a row to the "category" table then that category would be available to them when they went back to add a file. That sounds very confusing, but in the end I wanted to give the site administrators a way to add categories dynamically. I think now that this might be too much trouble and I should just hard-code it as you mentioned above.

function manage_files()
{
$data['title'] = 'Manage Files';
$crud = new grocery_CRUD();
$crud->set_subject('Files');
$crud->set_table('files');
$crud->columns('file_id', 'file_path', 'file_title', 'keywords', 'show_to', 'status', 'category');
$crud->set_relation_n_n('category', 'file_category', 'category', 'file_id', 'category_id', 'name', 'priority');
$crud->set_field_upload('file_path','..uploads/files/');
$output = $crud->render();
$this->_example_output($output);
}

This is what my Add Files looks like:

[img]http://www.bytheowl.com/georgia/add_files.jpg[/img]

I haven't created an organization for the files yet, they all go in one folder named '../uploads/files/ '. I hoped I could sort the files with grocery CRUD and just pull out the ones I wanted to share.

The ability to add files is only for site administrators, and that part seems to work well... at least the adding files works well, it is sorting the files when I want to get them back out that confuses me. So, the table that I am working on now (the last one in the picture above) is a table for users to be able to go to a page and view all the files in a certain category and then have the option to download a file. Because of your questions I suspect that I have added the files in a bad design and that I should start over on how I add the files and put them in categories. I would be much easier if the relation was 1_n



Your PS question about the search button not working:

The search button on 1_n relation will not work because I was sorting the results before I passed them to grocery CRUD to work with. I used:

//here I used the $user_submitted_category variable I get from the user to go into the category table and figure out which category_id they want.
$results1 = $this->get_data_model->getCategoryNumber($user_submitted_category);
foreach($results1 as $row){
$single_category_id = $row->category_id ;
}
//now that I have the $single_category_id that the user is looking for I queried the file_category table to get the file_id for all of the files that match up with the $single_category_id
$results2 = $this->get_data_model->getCDEListOfFiles($single_category_id);
$crud->where('status','active');
foreach($results2 as $row){
$crud->or_where('file_id', $row->file_id);
}
$crud->set_table('files');

So, as you can see I was able to get the function to loop through "or_where" statements to pull out the correct files... it worked well, but because these queries run every time the page is loaded it makes the search feature return all results every time. I do not get an error message from the search feature, it just keeps giving the same list.

I'm sure using a for each loop to construct a grocery CRUD function is not a best practice, I was just trying to figure out a way to make it work.

Sorry I am not great at explaining this situation, it is difficult to explain.

[jpeake]

Ohhh... I'm so slow... I finally understand what you are saying about the 1_n relationship... I have a little work to do to change what I've been doing. You are right, 1_n would be better, and store the category id in the file table. Thanks for your patience... a huge help.