[kenvogt]

I created a form that works fine (with the caveat noted below). I then tried to add set_relation(). It failed with a "324 No data received" error. When I looked at the processlist in mysql, I can see that it was trying to execute a statement that looked like this:

SELECT *

FROM (

`saying`

)

LEFT JOIN  `author` AS j4f331e2f ON  `j4f331e2f`.`id` = `saying`.`author_id`

Since there are 350,000 rows in 'sayings' and 75,000 in 'authors', I am not surprised that it timed out. Since the form will only show 25 items, why does this statement not include 'LIMIT 0, 25'? Even without set_relation() the form takes over 4 seconds to load, no doubt due to trying to load 350,000 rows before it processes them.

[web-johnny]

Do you use datatables theme? The datatables theme works with client-side scripting only. This means that ALL the data is loaded once.

If you have a huge amount of data you have to use flexigrid theme.

[kenvogt]

No, I am using the default theme.

[kenvogt]

I tried adding:

	  ->set_theme('flexigrid')

and I got a 500 error.

In case you are wondering, my directory tree is like so:
root
[indent=1]application[/indent]
[indent=1]assets[/indent]
[indent=2]grocery_crud[/indent]
[indent=3]themes[/indent]
[indent=4]datatables[/indent]
[indent=4]flexigrid[/indent]

(Sorry about the repeated edits, I messed up the indenting.)

[kenvogt]

This is turning out to be a dropdead problem for me. Is there any explanation for sending a query without a LIMIT parameter?

[kenvogt]

And why does ->set_theme() cause a 500 error?

[kenvogt]

I really need answers to these questions.

[web-johnny]

Probably the ->set_theme() causes 500 error as you have a timeout (datatables has client-side scrtipting so it tries to retreive ALL the data at once). The query without a LIMIT it is a query that I count with num_rows and not with count_all_results as I had some problems with the grouping results. Either way is the exact same thing. The only difference is that when you have num_rows you require more memory to the database as it stores the dump result to the memory.

There is no way to have COUNT(*) with grouping so I chose this solution. If you have problem with this you can change it from the application/models/grocery_crud_model.php (145-161 line) and change the code:

	function get_total_results()

	{

	 //set_relation_n_n special queries. We prefer sub queries from a simple join for the relation_n_n as it is faster and more stable on big tables.

	 if(!empty($this->relation_n_n))

	 {

	 $select = "{$this->table_name}.*";

	 $select = $this->relation_n_n_queries($select);

  

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

	 return $this->db->get($this->table_name)->num_rows();

	 }

	 else

	 {

	 $this->db->from($this->table_name);

return $this->db->get()->num_rows();

	 }

	}

with something like this:

	function get_total_results()

	{

  return $this->db->count_all_results($this->table_name);	

	}

[kenvogt]

I made the change you suggested and now it times out on this:

SELECT saying . * , (

SELECT GROUP_CONCAT( DISTINCT category.name )

FROM category

LEFT JOIN category_saying ON category_saying.saying_id = category.id

WHERE category_saying.category_id = saying.id

GROUP BY category_saying.category_id

) AS categories

FROM (

`saying`

)

[web-johnny]

Just don't have the relation_n_n column at your list and it will probably work.
For example if you have:

function film_management()

{

	$crud = new grocery_CRUD();



	$crud->set_table('film');

	$crud->set_relation_n_n('actors', 'film_actor', 'actor', 'film_id', 'actor_id', 'fullname','priority');

	$crud->set_relation_n_n('category', 'film_category', 'category', 'film_id', 'category_id', 'name');



	$crud->columns('title', 'description', 'actors' ,  'category' );

	$crud->fields('title', 'description', 'actors' ,  'category' ,'release_year', 'rental_duration', 'rental_rate', 'length', 'replacement_cost', 'rating', 'special_features');



	$output = $crud->render();



	$this->_example_output($output);

}

just change it to :

function film_management()

{

	$crud = new grocery_CRUD();



	$crud->set_table('film');

	$crud->set_relation_n_n('actors', 'film_actor', 'actor', 'film_id', 'actor_id', 'fullname','priority');

	$crud->set_relation_n_n('category', 'film_category', 'category', 'film_id', 'category_id', 'name');



	$crud->columns('title', 'description'); //<-- I just removed the 'actors' and 'category'

	$crud->fields('title', 'description', 'actors' ,  'category' ,'release_year', 'rental_duration', 'rental_rate', 'length', 'replacement_cost', 'rating', 'special_features');



	$output = $crud->render();



	$this->_example_output($output);

}

If you want the column at the list you have to optimize the query by using set_model or change the model of grocery CRUD.

[kenvogt]

Ok, thank you for investigating.

[kenvogt]

[quote name='web-johnny' timestamp='1338236857' post='2036']
{Use} something like this:

	function get_total_results()

	{

  return $this->db->count_all_results($this->table_name);	

	}

[/quote]

After working through some other issues, I did that which is noted above but it is still hanging up on the statement noted at the beginning. Is there any other place that this statement gets generated other than "function get_total_results()"?

[kenvogt]

Actually, the query in question is not exactly the same. Here is what PROCESSLIST tells me:


[color=#000000][font=sans-serif][size=3][left]Automatically appended backtick to the end of query![/left][/size][/font][/color]
[color=#000000][font=Verdan, Arial, Tahoma][left][color=#990099][b]SELECT[/b][/color] [color=#FF0000]COUNT[/color][color=fuchsia]([/color] [color=fuchsia]*[/color] [color=fuchsia])[/color] [color=#990099][b]AS[/b][/color] [color=#008000]`numrows`[/color]
[color=#990099][b]FROM[/b][/color] [color=fuchsia]([/color][/left][/font][/color]
[indent=1][color=#000000][font=Verdan, Arial, Tahoma][left][color=#008000]`saying`[/color][/left][/font][/color][/indent]
[color=#000000][font=Verdan, Arial, Tahoma][left][color=fuchsia])[/color]
[color=#990099][b]LEFT[/b][/color] [color=#990099][b]JOIN[/b][/color] [color=#008000]`author`[/color] [color=#990099][b]AS[/b][/color] j4f331e2f [color=#990099][b]ON[/b][/color][color=#008000]`j4f331e2f`[/color][color=fuchsia].[/color][color=#008000]`id`[/color] [color=fuchsia]=[/color] [color=#008000]``[/color][/left][/font][/color]

[kenvogt]

I got it to stop failing by commenting out the following from ./application/library/grocery_crud.php (around line 443):

if(!empty($this->relation))

foreach($this->relation as $relation)

$this->basic_model->join_relation($relation[0],$relation[1],$relation[2]); 

What problems might this cause?

[kenvogt]

Will this hack of mine cause any problems?

[web-johnny]

Yes without this the set_relation will not work at the list.