Use current value in database as option for where statement
Use current value in database as option for where statement
Koen Verhoeven
Posts: 25Questions: 7Answers: 0
in Editor
I've got a select list in my Editor which is based on active users. But I also would like to be able to edit historical records from users which are not active anymore. At the moment, when I try to edit to edit historical records, the select list does not contain the non-active users, so I'm not able to edit the records without getting an error. How can create a select list which contains all active users and the user based on the current database-value?
My current not-working code where I think 'TheCurrentUserIdFromTheDatabase' should be replaced with the current value. How to do this?
Field::inst( 'deenk_tijdschrijven_tijdschrijven.userid' )
->validator( 'Validate::dbValues' )
->validator( 'Validate::notEmpty' )
->options( Options::inst()
->table( 'deenk_tijdschrijven_users' )
->value( 'deenk_tijdschrijven_users.userid' )
->label( 'deenk_tijdschrijven_users.naam' )
->where( function ($q) {
$q->where( 'actief', 1, '=' );
$q->or_where( 'userid', 'TheCurrentUserIdFromTheDatabase', '=' );
} )
),
Thanks a lot for your help!
This question has an accepted answers - jump to answer
This discussion has been closed.
Answers
You are not describing what the "current value" really is and where you get it from. But you are right: you would need to replace "TheCurrentUserIdFromTheDatabase" with that value.
To help you I would need to understand your data model:
- relevant tables
- relevant relationships between those tables
- foreign keys etc
And I would also need to see more of your Editor instance.
- What is the parent table
- what is / are the linked tables / child tables etc.
When you say
TheCurrentUserIdFromTheDatabaseis that the currently logged in user? If so, do you have their user id in a session variable? If that is the case you could just replace that string with$_SESSION['userId'].Allan
@Alan , it is not the current logged in user, but the value currently stored in the database. @rft1234, see my complete php script below:
Oh - so it will change per row edited? That's a little more difficult since you'll need to get a new set of options for every row. That can be done using the
initEditevent, and make an Ajax request to the server to get the list of options for the row being edited. Then usefield().update()to set the options based on what is returned from the server.You'll also need a script at the server-side which will get the list of options for you. Unfortunately the Editor libraries don't provide that ability themselves, but it sounds like it should be a relatively simple SQL statement to get the data.
Allan
So the table with where de data should be stored is : deenk_tijdschrijven_tijdschrijven. The column with the userid is called userid
The referenced/parent table with the users is called deenk_tijdschrijven_users and also contains the column userid as primary key. It also contains a column for active users (deenk_tijdschrijven_users.actief = 1), and a column for the username itself (deenk_tijdschrijven_users.naam)
Koen, are you actually doing parent / child editing? When you do the editing of the child table do you have a parent record selected at that time? Because then you could simply pass the user id of the parent table to the server very easily and use this variable at the back end as well. That would make things a lot easier.
So if that is your use case just let me know.
Roland
It is just a select list, so no parent/child editing in the way you mean I guess. If you can help me with the very easy solution, that would be great:)
Maybe this helps:
Well, let me word it differently: Do you have this user id "available" at the front end at the time you are editing the record? (I mean you should have it available: How else would you know who this user "from the database based on the current database value" really is?!)
If you have that user available this would be the simplest solution, I guess.
(In parent / child editing you have it available automatically because it is part of the selected parent record.)
So assuming the parent user Id is available in the field "parentUserId" at the front end this would be the code:
In "ajax.data" you are sending the parentUserId to the server as a POST variable. I guess you would also need this in your Data Table definition as well, not only in the Editor instance.
Then on the server side you use the $_POST variable in your WHERE clause:
I think I'm missing something. I don't think I will need the same structure in my datatable definition since my table is retrieving the all required userdata with a join. My serverside code now is:
My js code is:
The editor shows the select list with all the active users, but does not show the current database value.
That has no connection: I mean you would need to pass the user id as a post variable. That is independent of the join you have server side.
Again: What exactly do you mean by "current database value"? Where is it in your code?
Are you really sure you are passing the parent user id in your code like this?
This makes no sense. You seem to be trying to pass back a variable as a post variable to the server that you are currently retrieving at the same time.
Once again: Without understanding what you mean by "current database value" and where you have it in your client side code I cannot help you.
My assumption was that it is the id of some parent table. Then you must have it available in your code client side. The fact that you are joining with the parent table in your server side code does not mean it provides you with that particular "current" user id.
Can you please also read this blog to understand parent / child editing a little better. It might help you to get some clarity on what you are trying to achieve:
https://datatables.net/blog/2016-03-25
No, I think it is not passing the value as I think it should work.
Maybe a screenshot works better:
As you can see in the table next to the editor, the first row shows the name the user 'Leon...'. But when clicking on Edit the username is not shown (at the location of the mouse pointer). This is because Leon is not an active user anymore. So what I mean with 'current database value' I mean that the value for Leon is present in the table, but I cannot retrieve it when editing because he is not present in the active user list.
Since you don't really seem to have a parent / child relationship between two tables you would probably need to handle this server side
not sure whether $data['deenk_tijdschrijven_tijdschrijven.userid'] is available inside the options instance at that time. You might want to give it a try.
I added the global var during some tests, but forgot to remove it.
Unfortunately $data does not seem to be available at that moment. Error:
Any other ideas. It should not be too difficult I guess
You would need to manipulate the options returned from the server on "select" of the table row. Just append the options returned from the server by the selected user id using the api:
https://editor.datatables.net/reference/api/field().update()
Use append: true.
Make sure you retrieve deenk_tijdschrijven_users.userid in your server side editor instance. Right now I only see you retrieving it INSIDE the server options instance which is NOT sufficient.
Thanks for all the hard work! Works almost perfect right now. It does work when I use the type 'select', but not with 'selectize'. I'll try to figure this out tomorrow.
Search the forum for selectize please. The options change is probably too late for selectize. It may be impossible or difficult to change them after initialization. In that case you would need to append the options on “xhr“ which isn't possible in your case because at that time the select of the table row hasn't been made ... Could be difficult. You might be forced to stick with the select field in this case ...
This is also the reason why the server side manipulations in the where clause of the options instance make no sense! All of this only works in parent / child editing, not in your case: The options are returned ONCE for ALL of the records of your data table. Hence you can only manipulate them AFTER selecting one record which obviously doesn't work with SELECTIZE. You would also need to make sure that when selecting a different record you change the options again because then the selected user is a different one. That might require that you save the original options in a global variable and every time you open Editor you append a copy of the original options with the respective user and replace (not append) the existing Editor options. A bit of work to do for you. To save the original options in a variable you'll probably have to intercept them on "xhr" like in this code example:
Then later you replace the Editor options with those copied options and the current user:
Thanks! As you already noticed I'm not really experienced with this. Writing the codes from your examples would take me hours:). I think I have to stick with the standard select for now, but I'm going to give it another try with the information from your last post. I'll let you know if it works. Many thanks again!