Editing a SQL VIEW containing GROUP BY Clause
Editing a SQL VIEW containing GROUP BY Clause
Khalid Teli
Posts: 251Questions: 71Answers: 0
I have created a simple SQL VIEW and used GROUP BY clause and read that using datatables.
Select member_name, product_name, product_code from contract_return_bal_forecast group by product_name
I know it is not possible to edit the records when using GROUP BY clause because of the unique id column complexities.
However, I need to find a way to edit the records on the table created by VIEW which uses GROUP BY clause.
What is the best way to do it?
I guess the only way will be save the results in new table and then use editor on that newly created table?
or is there any way in datatables I can do it?
Thank you
This question has accepted answers - jump to:
This discussion has been closed.
Answers
Let's step back for a second and confirm what it is that you need. Are you saying that you want to be able to edit a row in the grouped table - therefore possibly updating multiple rows in the original table?
If so, you would need some way to either identify the group, or to identify the rows within that group. Then you can apply an UPDATE statement to it based on whatever input you have.
That is not something our Editor libraries provide for I'm afraid.
Allan
@allan
Thank you.
I don't want to edit row in the grouped table.
What I want is , add additional information to the grouped table.
In other words, if sql View with group by clause gives me a table with 5 columns, I want to add two more columns where I can add add other relevant information .
(In the SQL query above, for each grouped product returned , I need to add column were Supplier can add price for those products as a group)
@allan
I was going through it and it seems, this suggestion given by you will work
However, I am not sure how to apply UPDATE statement.
I understand the logic but don't not know how and where to implement it.
Is it possible to provide a very basic example or hint?
That will he highly appreciated.
Thank you
I'm not sure how to apply an UPDATE statement in this case either! You say:
But where should it write the value to? The group might contain information from multiple rows, so should it write to them all, or to a new record or something else?
Allan
@allan
Thank you
When adding the information to the group, it should reflect in all the rows associated with that group.
In the image below, If I add the price for the the two groups (group1 and group2) it should reflect in all the rows which are associated with these two groups.
It would be very helpful to see an E/R-diagram of your database. But let me try guessing ... You have a table contract_return_bal_forecast that has all of those columns: product_code, product_name, pack_size, contract_prod, mfl, price.
Since you can't update the GROUP BY view you would need to set all the Editor fields on the back end to false. Then on "validatedEdit" (i.e. after all of the field validations have passed successfully) you can update the real table with your own SQL or using Editor's ->update method.
but you originally said:
That point still stands. Our Editor libraries on the server-side do not directly support what you are trying t do.
Your only option would be to have some custom code on the server-side which would be sent a list of the row id's that need to be updated, and then update them that way. @rf1234's code is a cunning way of doing that, but you need to make sure you get your grouping correct.
Allan
@allan
Thank you very much. I think I understand what you are saying.
I need to get row id's of all rows in a group and use an update statement based on those row id's.
However, I will need to get my head around and see how I can get the id's of rows in an group and update them accordingly.
@rf1234 thanks for the piece of code, it works perfect.
You guys have been of great help and the plugin is amazing. Thank you very much!
For the support, I tried to make a donation using this link https://legacy.datatables.net/donate but it shows page not found. Can you provide me a working link please?
Thank you, that's very kind. This page here, https://datatables.net/purchase/index , has a few options for donating.
Colin
@rf1234
As, I mentioned earlier the piece of code works fine but how would you proceeded when a SQL VIEW includes LEFT JOINS. As in my SQL VIEW
product_code and product_namecomes from a different table calledproductswhich is joined to'contract_return_bal_forecastusing LEFT join :in the code below , let us say, we are updating contract_return_bal_forecast table where product name in group = product name in the table
array( 'product_name' => $values["product_name"] )AND if the product_name come from different table and is joined using left join like this
->leftJoin( 'products', 'products.product_code', '=', 'contract_return_bal_forecast.product_code_fk' )In the case when I use joins, it shows unknown column 'product_name'
@allan
In terms of my database when I group by any column name (for example product_name). each grouped results will be differentiated by two variables product_name and start_date (which is start date of a contract). Which means everytime a group by clause is called each returned group will be differentiated by product_name and start_date which will be a kind of unique id for group.
so , in order to make the grouping correct can we use something like this in where clause
if you have multiple tables you would need to prefix the column names with the table name anyway I guess. Like 'yourView.member_name' and 'products.product_name'. If you don't do that Editor doesn't know that product_name is in a different table. It will search for it in "yourView" where it isn't found.
Then $values is different as well. Like this: $values["products"]["product_name"] etc.
It is good practice to always prefix the column names with the table name - even if you only use one table. Then you don't get confused ... Since I never use unprefixed column names I can't guarantee though ...
for more complex queries I would use the -.>raw() method. Here is an example:
https://datatables.net/forums/discussion/comment/179968/#Comment_179968
@rf1234
i used the prefix name for column names and everything works fine. However, on validateEdit function when using the prefix name as shown in code below it shows me the error
Notice: Undefined index: product_name in C:\wamp64\www\Editor-PHP-1.9.4\controllers\contract_return_forecast_supplier_fetch.php on line 67I assume I will have to use join inside the
editvalidatefunction and then use prefix. I tried different ways but couldn't manage to work it out. Can you please help Thank yousuppliergroupby2 is name of the sql VIEW
I assums I will have to do something like this but not really sure
Your update statement is wrong. You are referring to a different table ("product") but are not joining it.
Instead of $db->update you could use the raw method to execute your own SQL like this:
Don't know what this is:
If that is some kind of aliasing then $values["products"]["product_name"] might not work. You might have to use $values["product_name"] instead.
Ok, I found this in the docs that I wasn't aware of:
So, you would definitely need to use $values["product_name"] then.
@rf1234 Thank you
When using this
This works fine .However, after updating the opt_one = 'xxx value' , it sjhows me error:
Should
<?php I'm not sure what your SQL server will make of `a opt_one`. ?>SET a opt_one = :opt_one,be:Or if
opt_oneis unique as a name across the two tables just useopt_one.Allan
Sure, Allan is right! I forgot the periods.
I have a very similar SQL statement in my code. I remember it was important to have the INNER JOIN before SET. Otherwise it didn't work ... and of course you mustn't forget the periods between alias and field while you can't have periods between table name and alias. This can be confusing at times ...
@allan @rf1234
While using
SET a.opt_one = :opt_one,
It give some error ,
somehow it is confusing between
suppliergroupby2andcontract_return_bal_forecastAre you sure the error is caused by this statement? And not by Editor?
Hi @rf1234
I am sorry if I ask too many stupid questions. How would I know if the error is by editor? not the particular statement?
Having said that, Just for the test purpose, I used the simple Update sql command without where clause and it is working as it is supposed to be . On this basis can we say there is no problem with editor?
Thank you
No, not really. suppliergroupby2 is not in the UPDATE statement. Unless contract_return_bal_forecast is some kind of alias of suppliergroupby2 the UPDATE statement cannot have caused this error.
It is much more likely that something in your Editor instance caused the error, e.g. a missing or syntactically wrong ->set( false ).
This here from your code above has a syntax error that your IDE should have shown you and which you should have seen in your browser's console, too.
The error is the final comma after the last ->set( false ). And surprise: This is exactly in conjunction with a field definition of the view in question: suppliergroupby2. That makes me suspicious that Editor caused the error.
In addition you could have tested the UPDATE statement interactively with MySQLWorkbench, PHPMyAdmin or whatever you use. All you would have needed to do that is to copy this into the tool and replace the host variables with real values.
That would have shown you immediately whether or not the UPDATE statement has a syntax error. I would have done that too if I had your database available. It is good practice to develop all of your SQL interactively and only copy tested statements into your code as embedded SQL because IDEs won't show you SQL syntax errors.
If that doesn't help either I suppose all you could do is to give me or @allan a link to a test page showing the issue.
@rf1234
I guess It is working now, updating all the fields in
contract_return_bal_forecastbased onvalues given in
'suppliergroupby2view.Only issue is every time I update a row, I have to manually input the product name instead of it reading itself from the field.
below is my code and it works fine.
The only issue with this is when I use
and then on client side when I use
It does not read the product name, I have to manually put it and it works like that.
I think there are a couple of problems with your approach.
- Why do you need the grouping approach and why do you need to do a mass update? The reason is that your data model is not normalized meaning you save a lot of redundant data like product name opt_one_price etc. multiple times.
- If you had your data model structured properly according to the principles of entitiy relationship modeling you wouldn't need any of this. You would simply join your tables and done.
- Having redundancies like this will cause serious trouble because you will have to control the redundancies "manually". You will make mistakes and you will face a mess in your database. I am pretty sure this will happen.
But anyway ... looking at your code there are a few inconsistencies:
- PHP: You are not joining table products but you seem to be willing to save the product name redundantly ... With proper data modeling you would only save the id of the product as a foreign key. With your approach you will have update anomalies: If the product name changes you will have to manually make sure that all of the redundantly saved product names across all of your tables are updated! That will be difficult - and completely unnecessary with proper modeling.
- Javascript: You want to use a field called "products.product_name" that you don't retrieve server side. That can't work.
All in all I think you need help ... but not the kind we can provide here in the forum I am afraid. You would need training with data modeling first, at least that would be my recommendation. Good luck! I keep my fingers crossed.
@rf1234
I will try my best to expain using the pictures below.
I have two tables in database
Productsandcontract_return_bal_forecast. Products contains all the basic product information and other table contains the contract terms and price for the products under contractIn contract_return_bal_forecast table:
Opt_one, opt_two and opt three corresponds to different product packing types.
For example, opt_one can be = Mixed full container
Opt_two can be = 2-4 pallet size
and Opt_one_price, Opt_two_price corresponds to the price for those quantities
Note: Product_code and Product_Name (and any other relevant information) comes from products table using foreign key (Product_code_fk).
Now we must send this list to suppliers to get price, but we send them as a group not individual product. That is why I use group_by clause to group products by name
So, I create a groupbysupoplier view, which groups by
product_nameand looks like below andThis table then gets send to suppliers to put price in, once the price gets inserted then I want this price to be automatically updated for each individual product in Contract Return Balance Forecast table.
in the sql view I created , I joined the products table with contracts_return_bal_forecast table using
So when I was reading the this view from php script , I was expecting the product_name to fetch using this piece of code in sql view
crg_intranet`.`products`.`product_name` AS `product_namewhich didn't work.I am not sure, if I need to left join it again in php script? that is why I was confused
Hi @rf1234
Everytghing works fine now Thank you very much.
There were three things that were causing the problem
1)Using the wrong foreign key
product_codeinstead of usingproduct_code_fk2) not setting the fields values read from SQL View to false
->set(false),which created this errorerror: "An SQL error occurred: SQLSTATE[HY000]: General error: 1288 The target table suppliergroupby2 of the UPDATE is not updatable"3) Onlient side in editor field not using all the fields used inside update command on serverside.
After fixing these two problems, It works fine
server side code:
And on client-side in editor field it looks like