|
Poznámky k chybe |
|
|
|
Yes this is interesting features, however changes are not too huge.
Maybe we can integrate these two options into core class, hm?
And btw. can you describe some real world example how to use this? I understand how it works, but it is difficult for me to imagine situation, when it is neccessary (and ['values'] cannot be used). |
|
|
|
hbernard
|
|
2004-04-11 19:32
|
|
I don't understand : what can't you do with current pme options.
Your sample seems to be doable with current API.... |
|
|
|
hbernard
|
|
2004-04-11 19:35
|
|
And, feature request is, finally :
foreign is same as values, except 'select' is not set to 'D' ?
This is the case currently, no ? (values don't fore
ce to 'D')
Or did I miss something ? |
|
|
|
|
You may all have a point - perhaps it is doable in the existing schema, but I couldn't get it to work - every combination I tried gave me an SQL error of some sort. The key thing is that this is used in the situation where you don't want a 'D' field - you explicitly don't want a drop down, you want a normal text entry field which joins to another table. I couldn't work out how to get the join in without forcing this field to be a dropdown. If I've missed something, then please accept my apologies for wasting everyone's time.
By the way, 'values' does force the field to be a dropdown. Maybe the solution should actually be not to do that, but to allow the user to explicitly specify 'D' if they mean it. I didn't want to change that though as it would break backward compatibility.
I agree that it would be more sensible to put it in the main class, it is really only a minor change.
I actually did it to solve one of the problems on the forum as an intellectual exercise (although not a very difficult one as it turned out). I've seen a number of situations on the forums where someone has wanted to include a field from a joined table either in an 'sql' expression or as a virtual field (same thing I guess), but maybe the joined field has not been required to be a 'values' list.
I tend to agree with you that it is going to be very rarely needed. The best example I can think of is where you want someone to enter some form of user name into a record and you need to pull up some other details about that user from another table (like full name, permissions etc etc). You probably wouldn't want a pull down for security reasons, but you still want the link to the other table.
edited on: 2004-04-11 20:08
edited on: 2004-04-11 20:13 |
|
|
|
|
It took me a while to understand what this extension does. But now I am using it.
This extension allows phpMyEdit to display fields that are JOINed to the main table. This is very useful. For example it allows me to show this table:
<pre>
+----+--------+------------------------+
| id |shelf_id| description |
+----+--------+------------------------+
| 1 | 1003 | Colored boxes |
| 2 | 1003 | Office supplies |
| 3 | 1004 | Junk! |
| 4 | 1003 | Beanie Babies |
| 5 | 1003 | Office supplies Box #2 |
+----+--------+------------------------+
With proper names rather than ID numbers:
+----+----------------------+------------------------+
| id | shelf_name | description |
+----+----------------------+------------------------+
| 1 | Top shelf in hallway | Colored boxes |
| 2 | Top shelf in hallway | Office supplies |
| 3 | Rack in basement | Junk! |
| 4 | Top shelf in hallway | Beanie Babies |
| 5 | Top shelf in hallway | Office supplies Box #2 |
+----+----------------------+------------------------+
</pre>
Of course I'd also like to be able to edit the JOINed table columns, but see bug #220 for more on that. |
|
|
|
hbernard
|
|
2004-04-12 12:30
|
|
ajh,
As said, this is doable with current API :
Currently, 'values' join is not performed if there is no description field.
(in fact, there must be a 'column' AND a 'description' field.
So you have to write
'values'=>Array( //values in place of foreign
'table' => 'items',
'column' => 'id',
'description' => 'id' //this is to force the join.
)
Then, join is performed.
Still, the field is a dropdown (because col_has_values).
So, if you want to have an entry in place of the dropdown, you need to do it differently :
Have an hidden virtual field that is doing the custom join :
$opts['fdd']['dummy_field'] = array(
'input' => 'VR',
'options' => '',
'values' => Array(
'table' => 'items',
'column' => 'id',
'description' => 'id', //useless, but required by the implementation
'join' => '$main_table.id = $join_table.id')
);
And then, as usual....
But this sample won't work currently because there is a bug in implementation of custom join (see #134, I just uploaded patch)
IMHO, that's the way we should do this. No need for API addition.
Issues we have to resolve are :
1- Is it Ok like this, or do we need 'foreign' API ?
2- If 'foreign', can we just let act as 'values' except for automatically set dropdown.
3- Join should be performed even if no description field is set.
4- custom join should be performed even if no 'column' field is set. (but this go to the other bug report)
5- we have to be more accurate about the 'select' option. Currently, this seems to be used mainly for the filter view. |
|
|
|
|
ajh: you have to try ['values|LF']
This enables ['values'] on the List/Filter pages, and there is no drop down on the record edit pages. ;-)
See Options variability documentation for more information:
http://platon.sk/projects/doc.php/phpMyEdit/html/configuration.options-variability.html |
|
|
|
|
|
I'll test it when it works, and even help write documentation in English. |
|
|
|
|
Wow - didn't think my little extension would cause so much controversy!!
I've just tried "values|LF" - it doesn't work because the joined table is then not included if you try and do a Change so you get a "PMEjoinX table not found error". I really have tried every combination I can think of, and I'm pretty sure that this can't be done in any other way.
However - I do agree with hbernard that the current API should be able to do it without this addition. In reality, the only change that needs to be made is that having a 'values' option on a field should NOT force it to be a drop-down. Also - I'm not sure that it works if the field is read-only or hidden (which it should if this is to work).
Anyways - anyone is welcome to use the modified code, and I guess it is up to nepto whether he thinks this is worth addressing or not. |
|
|
|
|
Sorry - also meant to address hbernard proposal...
I have't tried the suggested solution although I can see how it can work. However this seems to be a completely obscure way of achieving something that ought to be simple. I'm a reasonable competent user of PME myself and I have to say that I would not have spotted that one. So - I would agree that maybe it is possible to do in some obscure way, but do you really want users to have to understand in the inner workings of PME as much as this solution would assume that they need to. I, personally, would go for doing something to the API to make it easier - but then, it's not my decision ;-) |
|
|
|
|
ajh: just short note -- maybe controversy, but very helpful for the project
I agree that proposed workaround is not the absolutelly best solution, but it makes a sence. JOIN is done only when *other* field from lookup table (description field) is needed. I consider this behaviour as desirable.
I agree also that table joining part of documentation needs some improvements.
It is because phpMyEdit users are performing the more and more difficult JOINs, and API seems not to handle all the issues correctly. But is up to us to correct these.
I can also imagine that "PMEjoinX not found" errors could be very annoying. We, the core developers, who know how phpMyEdit works, can these easily fix for ourself, however it may be problem for common user.
We currently added some variables into new ['join'] option.
We have to add similar into $opts['filters'] as well.
And at least we have to also check if common usage of ['values']['table'], ['values']['column'] and ['values']['...etc...'] do not produce "PMEjoinX not found" error(s). |
|
|
|
|
|
An example is needed in the documentation, to make it clear that 'values' can be used in this strange way. |
|
|
|
|
some OT notes from me:
$opts['filters'] and also $opts['col_name']['sql'] should have the same variables as ['join'] option. something like $join_col_name or $join['col_name'] representing: name of the join table as defined by 'values' in column 'col_name'.
maiking join with text input: this could be done by before trigger (which will try to find a corresponding pair and return false if not successful). |
|
|
|
hbernard
|
|
2004-04-13 12:23
|
|
The feature request is :
how can we have an entry on the joining field AND join still performed ?
API proposal (see the #223):
1- 'input' has precedence to 'select' (BC)
2- 'input' can have 'D' or 'M' or 'T' as additionnal flag (BC)
3- if 'values' 'table' is set AND 'values' 'column' is set : ALLWAYS PERFORM JOIN (BC, if there is no side effect)
4- if 'input' contain 'T', then force to entry,
Then, we can have the feature requested by ajh, but stick with BC API
edited on: 2004-04-13 12:25 |
|
|
|
hbernard
|
|
2004-04-13 12:34
|
|
Category changed to core / field option.
We all agree this has to go in the main class, whatever the way we go. |
|
|
|
|
Concerning to:
3- if 'values' 'table' is set AND 'values' 'column' is set : ALLWAYS PERFORM JOIN (BC, if there is no side effect)
Althought join should be made only when really neccessary... this seems to be logical. If someone want to print simple value (even if this value is a foreign key to external table), he/she just should not use ['values']['table/column'].
However I'm not sure what the JOIN above is good for. |
|
|
|
hbernard
|
|
2004-04-14 21:26
|
|
Rationales for the (3) :
(a) Either it is "more logical" to make the join only when necessary.
then, ajh will have to write
'values'=>Array(
'table' => 'items',
'column' => 'id',
'description' => 'id' //this is to force the join.
.... and this is "not logical" to force the join like this.
(b) either it is "more logical" to have join performed when 'table' and 'column' are used, even without description.
.... and this is "not logical" to have a join done without visible use of it.
As you see, this is maybe API cosmetic issue.
I vote for (b), because
- PME power users won't do b without good reason (have a join usable in other fields)
- PME power users won't find easily the trick of 'description' = 'column' (Ajh didn't found it, and I think he is not alone).
Again, this is cosmetic.
The real issue here is (4), which is doable because (1) and (2) |
|
|
|
|
Currently, if ['values']['table'], ['columns'] and ['description'] are defined, PME does the following:
1. Add table to FROM clause
2. Add join into WHERE clause
3. Force field to be SELECT field
If any of these are not defined that it does nothing. I think that you can exploit this to make a slight change to the behaviour that preserves backward compatibility but also allows the user to introduce joined fields into their 'sql'/'sqlw' options without forcing a field to be a SELECT field.
I suggest the following:
If ['values']['table] and either of ['values']['columns'] or ['values']['join'] are set then:
- Add table to FROM clause
- Add join to WHERE clause
- All columns from joined table are now available to use in 'sql'/'sqlw'
If in addition ['values']['description'] is set then:
- Force field to be SELECT (as now)
You would still have to set 'description' to be the same as 'column' if you wanted to use 'column' as the data in your pulldown, but that makes sense anyway.
I'm pretty sure that this is a trivial change and I don't think that it would break any backward compatibility as it exploits a scenario where PME today does nothing.
edited on: 2004-04-15 08:59 |
|
|
|
|
After thinking about this, and working on bug #220 also, I feel a new API is justified here. A new API might not do much that is new, but it will make it much easier to understand.
Here is a version patched against current CVS. I called it a "join" instead of "foreign", because that seems easier to understand:
diff -u -r1.124 phpMyEdit.class.php
--- phpMyEdit.class.php 8 Mar 2004 09:45:34 -0000 1.124
+++ phpMyEdit.class.php 22 Apr 2004 02:33:03 -0000
@@ -527,6 +527,17 @@
$table = $this->fdd[$field]['values']['table'];
$id = $this->fdd[$field]['values']['column'];
$desc = $this->fdd[$field]['values']['description'];
+
+
+/* foreign mod start here */
+ if ($id == '' && $table == '') {
+ $table = $this->fdd[$field]['join']['table'];
+ $id = $this->fdd[$field]['join']['column'];
+ $desc = $id;
+ };
+/* foreign mod finishes here */
+
+
if ($desc != '' && $id != '') {
$alias = 'PMEjoin'.$k;
if (!in_array($alias,$tbs)) {
To create a join:
$opts['fdd']['stop_id'] = array(
'name' => 'Stop ID',
'select' => 'T',
'maxlen' => 11,
'default' => '0',
'required' => true,
'join' => array( 'table' => 'stop', 'column' => 'stop_id' ),
'sort' => true
);
To use joined fields:
$opts['fdd']['Street'] = array(
'name' => 'Street',
'select' => 'T',
'input' => 'V',
'maxlen' => 40,
'sort' => true,
'sql' => 'PMEjoin1.Street'
);
Joined fields are read-only. |
|
|
|
|
Need to be careful using "join" as it is already a sub-parameter of "values" in the CVS version. The difficulty I have is that it is just doing the same thing as the existing "values" parameter so this is duplication. The only change that we really need to do is to prevent doing a join forcing a field to a select type and to ensure that the join always gets done whether the field is readonly, virtual etc etc. That's why I suggested that you just use the "description" tag to modify the input field behaviour and you leave it out if you don't want that. This keeps it backward compatible.
edited on: 2004-04-22 10:56 |
|
|
|
|
There is value in making the API less confusing.
Use of the word "join" is desirable, since everyone who wants to do a join already knows the term "join". |
|
|
|
|
I don't know if you want my input or not but here it is anyway. I was the one who originally asked for some way to do this and ajh was kind enough to do if for me.
I think this should be kept as simple as possible. That means adding a new "API" as suggested above. Even though the existing API can be (possibly) modified slightly to accomplish this, using it like that would be so unintuitive that no one but you guys who developed it can understand how to use it.
What does it matter after all if this duplicates (maybe) some existing functionality? One of your goals should be to make the product as easy as possible to use and understand.
I'm with brycen - "join" is the word to use.
By the way can you do a global search and replace and STOP using ['col_nam'] in the docs? This is extremely confusing to a new user (trust me). Replace it with [<column name>]. Is change so difficult? |
|
|
|
pilavdzic
|
|
2004-10-30 06:33
|
|
I am also a new user and new to the forums, and after reading TONS of posts about this, bug reports, etc, still have not gotten a clear explanation on how to do this. I wish to display fields from other tables. This is probably the most common thing in existance that anyone would want to do at some point. There is no need to add existing functionality if you give a clear example on how it works. All the examples above are clear as mud.
For example: hbernard said above:
>As said, this is doable with current API :
>Currently, 'values' join is not performed if there is no description field.
Thank you. This is useful information.
>(in fact, there must be a 'column' AND a 'description' field.
What is the column field for? The description field seems to be for the ID that joins them, from your example below, but can you elaborate on this? Where is this documented?
>So you have to write
>'values'=>Array( //values in place of foreign
>'table' => 'items',
>'column' => 'id',
>'description' => 'id' //this is to force the join.
>)
>Then, join is performed.
How? By what? If you give what command? Or does the command above do this?
>Still, the field is a dropdown (because col_has_values).
Why does col_has_values mean the field is a dropdown? What is col_has_values?
>So, if you want to have an entry in place of the dropdown, you need to do it differently :
Yes, this is exactly what I believe we all want. What does the above do if not this? Generates what kind of dropdown? What is the purpose of this dropdown, when you select things from it, where do the values go? I am totally confused. But lets go on, because it seems like now you are starting to talk about what I want to know.
>Have an hidden virtual field that is doing the custom join :
Why?
>$opts['fdd']['dummy_field'] = array(
>'input' => 'VR',
>'options' => '',
>'values' => Array(
>'table' => 'items',
>'column' => 'id',
>'description' => 'id', //useless, but required by the implementation
what is column for and what is description for exactly, and why are they the same
>'join' => '$main_table.id = $join_table.id')
>);
What does all this accomplish?
>And then, as usual....
Do what as usual? Could you give the full example, there is no 'as usual' because this usually dosn't work for most people for one reason or another.
>But this sample won't work currently because there is a bug in implementation of custom join (see #134, I just uploaded patch)
So it doesn't work? Or now that it is patched it does work? Does it work or doesn't it? If I am trying to implement something that does not even work how do I know if I made a mistake?
" |
|
|
|
hbernard
|
|
2004-10-31 23:54
|
|
pilavdzic, I understand you have lot of questions, but some of the answers are just in the documentation. (the 'column' and 'description' questions, for example)
And the others are related to the cvs version.
Which means you have to accept that answer is not easy.
Please, can you adopt a less-aggressive style of writing.
The piece of code above was not designed to teach PME to anybody, but to demonstrate something to ajh and brycen and nepto and all of those that already know how PME work internally.
it works.
This doesn't mean it's the good way for PME, but this wasn't the very first goal. |
|
|
|
pilavdzic
|
|
2004-11-01 15:10
|
|
Again I apologize for posting in too many places and if the posts seemed agressive. You answered a lot of my questions in the other thread, but the one thing you continue to indicate that I have not yet found is about the documentation... Perhaps I was unable to find the latest documentation to which you are referring. I dowloaded it off the web site, I couldn't find it in the CVS.. is that my problem? Because I did read all the documentation, and although there may have been some answers in there, I still didn't understand those things that I had questions about above. Now I understand them more after your reply. Thank you. I know it is unreasonable to want an example of everything but I learn much better by example so maybe that is what helped me.
Thanks again, and sorry. |
|
|
|
hbernard
|
|
2004-11-01 21:38
|
|
[pilavdzic, don't be sorry, it seems some docs I made about 'V'(rtual) fields are not in the CVS. Sorry. :-/]
OK, except the newcomers, I'm pretty sure now that every one has made an opinion on this.
What was the bug's goal ?
MAKE A JOIN WITHOUT HAVING A DROPDOWN ON THE JOINING FIELD
Although it's doable with current API, I know this is not really a good solution.
1. First step :
I'm sure we should implement ajh proposition.
(making the join requires only 'column' set.)
2. Second step :
as brycen pointed out, current API is confusing for SQL-educated people.
If they want to do a join, they are just searching for the join option.
~brycen merged with ~ajh proposal :
- New API : "make_join" => Array('table', ....)
- New API honors 'input' flags (D/T)
- 'values' lookup is just a wrapper to "make_join" that
- forces INPUT to 'D' when 'description' is set
- sets 'sql' option correctly when using extended description design.
in fact, 'values' is already a wrapper for 'values2', so this make sense.
This may help to get rid of 'values' fuzzy API (which allow fixed set of values or table lookup)
values API is fuzzy :
- values => Array('foo1', 'foo2', 'foo3') is a simple enum set.
- values => Array('table' => .....) is a join API.
Nepto, ajh and brycen (and others, too) what do think of it ?
Proposal B :
this may be an additional API or replacement for proposal A.
implement real multi-table support for PME
what do you think of having an extra API
$opts['static_joins'] = Array(
'my_join_alias' => Array('table' =>..., 'join' => ...),
'another join' => Array(...)
);
and
$opts['fdd']['field_name'] = Array(
...
'from_table' => 'my_join_alias', // default is PMEtable0
...)
Would make easier to implement multi-table update, no ? |
|
|
|
brycenesbitt
|
|
2005-03-05 17:48
|
|
I'm afraid I got a little lost on your proposal. Is there a simpler way to make the API. Maybe:
$opts['tb'] = 'table1,table2,table3';
$opts['join_statement'] = 'tabel1.xid = table2.xid AND tabel1.xid=table3.xid';
$opts['join_columns'] = 'street, address, postal_code';
or even simpler:
$opts['fdd']['xid'] = array(
'name' => 'Stop ID',
'select' => 'T',
'required' => true,
'join' => array( 'table' => 'table2', 'column' => 'xid' ),
'sort' => true
);
Then the extra columns are treated like any other (read-only) column. |
|
|
|
|
OK, we can go with 1, it is straightforward.
That we should consider implementation details on 2a, 2b is definitely not a right way to go. |
|