This forum is in READ-ONLY mode.
You can look around, but if you want to ask a new question, please use the new forum.
Home » development » Miscellaneous » Propel help for beginners - version 1
Propel help for beginners - version 1 [message #30399] Wed, 27 June 2007 22:41 Go to next message
halfer  is currently offline halfer
Messages: 9535
Registered: January 2006
Location: West Midlands, UK
Faithful Member
Hi all

Partly to give me an excuse to use version 1.0x, and partly to help beginners with the frustration of constructing Propel queries, I have written a small app to convert pseudo-SQL into Propel code. It also gives me a chance to put my new virtual host through its paces Smile

If anyone would like to, please have a go: simply put in a query, and hit the Generate button. If you have any feedback, feel free to post it here. Is the generated code correct? What would be the most useful first improvements for Propel beginners?


Remember Palestine
Re: Propel help for beginners - version 1 [message #30412 is a reply to message #30399 ] Thu, 28 June 2007 03:16 Go to previous messageGo to next message
leftpeg  is currently offline leftpeg
Messages: 39
Registered: April 2007
Member
Cool, initial impression is that it looks very useful.
icon14.gif  Re: Propel help for beginners - version 1 [message #30413 is a reply to message #30399 ] Thu, 28 June 2007 04:42 Go to previous messageGo to next message
phuson  is currently offline phuson
Messages: 16
Registered: March 2007
Location: California
Junior Member

Haven't tested it much out yet but it does look pretty useful and quite promising.

And I might add, this looks like a good way for me to learn Propel too. Very Happy

[Updated on: Thu, 28 June 2007 04:44]

Re: Propel help for beginners - version 1 [message #30506 is a reply to message #30399 ] Fri, 29 June 2007 16:08 Go to previous messageGo to next message
halfer  is currently offline halfer
Messages: 9535
Registered: January 2006
Location: West Midlands, UK
Faithful Member
OK, great. The main thing I need to fix is that defining a Criterion and then adding it separately is a bit lengthy when $c->add(column) in some cases will do the trick. But it's a start! When I get a moment here and there, I'll do a bit more on it.


Remember Palestine
Re: Propel help for beginners - version 1 [message #30570 is a reply to message #30399 ] Mon, 02 July 2007 08:19 Go to previous messageGo to next message
snowkrash  is currently offline snowkrash
Messages: 518
Registered: July 2006
Location: The Netherlands
Faithful Member
Great! This tool will make the adoption threshold a lot lower. There's a lot to be done, I see.

Would you like some help from the community?

If you're code is in a SVN repository ... people can help you create the Ultimate Propel Criteria Generator ... you'll have to think about a proper name Very Happy
Re: Propel help for beginners - version 1 [message #30574 is a reply to message #30399 ] Mon, 02 July 2007 10:13 Go to previous messageGo to next message
halfer  is currently offline halfer
Messages: 9535
Registered: January 2006
Location: West Midlands, UK
Faithful Member
Glad you like Very Happy

Community help would be very welcome, and I'd be happy to release the code for folks to work on. I expect I'd release it initially as a tarball for simplicity, although in due course I guess I will have to work out how to install SVN on my server Rolling Eyes


Remember Palestine
Re: Propel help for beginners - version 1 [message #30589 is a reply to message #30574 ] Mon, 02 July 2007 10:47 Go to previous messageGo to next message
snowkrash  is currently offline snowkrash
Messages: 518
Registered: July 2006
Location: The Netherlands
Faithful Member
Isn't it possible to host it on the Symfony SVN server?

I'll bet that Fabien en co. will applaud these kind of initiatives that help the adoption of Symfony and for that reason they may help you with some space on the SVN server.

And I have some/modest experience building and managing SVN repositories. So, helping out won't be a problem.
Re: Propel help for beginners - version 1 [message #30591 is a reply to message #30399 ] Mon, 02 July 2007 10:56 Go to previous messageGo to next message
halfer  is currently offline halfer
Messages: 9535
Registered: January 2006
Location: West Midlands, UK
Faithful Member
That's a good idea, I'll ask them; it would be simpler than trying to run a repository myself!


Remember Palestine
Re: Propel help for beginners - version 1 [message #30705 is a reply to message #30399 ] Tue, 03 July 2007 21:29 Go to previous messageGo to next message
halfer  is currently offline halfer
Messages: 9535
Registered: January 2006
Location: West Midlands, UK
Faithful Member
Fabien has suggested that this ought to become a plugin, and as such has kindly made space available in the plugins section of the symfony SVN server.

I don't have time at present to figure out how to convert it, but hope to at some point. Should anyone like to see the code, or even make some improvements in a non-plugin form, they would be welcome to. The code as it stands is here. Tear it to shreds Wink

[Updated on: Tue, 03 July 2007 21:31]


Remember Palestine
Re: Propel help for beginners - version 1 [message #31861 is a reply to message #30705 ] Fri, 20 July 2007 11:40 Go to previous messageGo to next message
seppal  is currently offline seppal
Messages: 24
Registered: July 2007
Location: Bayern/Bavaria
Junior Member
Hi,

I tried to generate the criteria api calls with this sql-statement but this didn't work.

person.gender = 'M' AND (person.location = 'Birmingham' OR person.location = 'Manchester')


The error message is:

Error: Unrecognised boolean operator ''
[/ERROR]

But its a good idea and a great tool.

Keep it up!
Re: Propel help for beginners - version 1 [message #31863 is a reply to message #30399 ] Fri, 20 July 2007 12:05 Go to previous messageGo to next message
halfer  is currently offline halfer
Messages: 9535
Registered: January 2006
Location: West Midlands, UK
Faithful Member
Thanks seppal, I'll fix that issue first. I'd not spotted that, I guess the bracket parsing must have a bug in it! I appreciate the report Smile


Remember Palestine
Re: Propel help for beginners - version 1 [message #31868 is a reply to message #31863 ] Fri, 20 July 2007 12:40 Go to previous messageGo to next message
lajkonik86  is currently offline lajkonik86
Messages: 190
Registered: June 2007
Location: The Netherlands
Senior Member
im not that far in the learning process yet, but looks very useful great job !
Re: Propel help for beginners - version 1 [message #32344 is a reply to message #30399 ] Thu, 26 July 2007 22:20 Go to previous messageGo to next message
halfer  is currently offline halfer
Messages: 9535
Registered: January 2006
Location: West Midlands, UK
Faithful Member
seppal: the bug you raised is now fixed, plus I've added support for additional operators. Potential contributors: I've not had a chance to convert it to a plugin yet, will do so soon.


Remember Palestine
Re: Propel help for beginners - version 1 [message #32366 is a reply to message #32344 ] Fri, 27 July 2007 08:09 Go to previous messageGo to next message
seppal  is currently offline seppal
Messages: 24
Registered: July 2007
Location: Bayern/Bavaria
Junior Member
Thanks,

good work, good tool. As a beginner of Symfony I learned to work with Criterias.

Only for your Information:
SQL-Statement:
(person.firstname = 'vorname1' AND person.lastname = 'nachname1') OR (person.firstname = 'vorname2' AND person.lastname = 'nachname2') AND person.location = 'ort'


Error:
Error: Can't merge two groups of differing boolean operators (OR, AND)

Re: Propel help for beginners - version 1 [message #32374 is a reply to message #30399 ] Fri, 27 July 2007 09:58 Go to previous messageGo to next message
halfer  is currently offline halfer
Messages: 9535
Registered: January 2006
Location: West Midlands, UK
Faithful Member
Yes, that behaviour is deliberate. With the brackets in place, your expression is evaluated as "A OR B AND C", where A and B are compound expressions and C is a simple expression. The problem the parser has is that it doesn't know if you mean "(A OR B) AND C" or "A OR (B AND C)".

Use brackets to indicate precedence and the error will disappear. There is a note on the page to this effect, but perhaps it's not clear enough (and I think I'll reword that error message, which doesn't make a great deal of sense).

Keep the bug reports flowing though, very useful Very Happy


Remember Palestine
Re: Propel help for beginners - version 1 [message #32376 is a reply to message #32374 ] Fri, 27 July 2007 10:10 Go to previous messageGo to next message
seppal  is currently offline seppal
Messages: 24
Registered: July 2007
Location: Bayern/Bavaria
Junior Member
Oh, sorry, my mistake.

Your parser is good, in opposition to me. Laughing
Re: Propel help for beginners - version 1 [message #32377 is a reply to message #30399 ] Fri, 27 July 2007 10:14 Go to previous messageGo to next message
Piwaï  is currently offline Piwaï
Messages: 40
Registered: July 2007
Member
Nice tool Wink

about A OR B AND C :

I know it won't be easy, but it should work. It should be defined as a default rule for the parser.

You should read some documentation about how to create a compiler or an interpreter (which is in fact what you created).

For instance, your parser should be able to transform :
A OR B OR C -> A OR (B OR C) -> parsed into propel code.

It just a question of rules : should the code be read from left to right or right to left, and what operator have precedence over the other. ex : A OR B AND C -> A OR (B AND C) -> parsed.


I really appreciate this tool, and I don't want to give you too much work Wink . But I would rather consider this "deliberate" behavior as a bug, not a feature Wink . Hope you'll find how to fix it.

Good job anyway !!

Bye

Piwaï

Edit : it's a same when interpreting some mathematics :
A + B * C + D * E + F
You know that * has precedence over +.
Then, step by step, here is how it is parsed :

From left to right :
A + B * C + D * E + F

A + (B * C + D * E + F)

A + ( (B*C) + (D*E + F) )

A + ( (B*C) + ( (D*E) + F) )

From right to left :
A + B * C + D * E + F

(A + B * C + D * E) + F

((A + B * C) + (D * E)) + F

((A + (B * C)) + (D * E)) + F

You may do this using recursive functions.

[Updated on: Fri, 27 July 2007 10:26]

Re: Propel help for beginners - version 1 [message #32385 is a reply to message #30399 ] Fri, 27 July 2007 13:02 Go to previous messageGo to next message
halfer  is currently offline halfer
Messages: 9535
Registered: January 2006
Location: West Midlands, UK
Faithful Member
seppal: no probs Smile

Piwaï: I did consider that approach, and I'm not totally opposed to it. It's a convention in programming languages that AND and OR have the same mathematical precedence, much like plus and minus. On this basis, they would be evaluated left-to-right. (Edit: I have found that, in SQL Server, AND has a higher precedence.)

However, this change won't be very high on my priority list, as there is an acceptable work-around available (using brackets to indicate precedence) and meanwhile there are plenty of other features I'd like to work on first. Of course once I release the code as a plugin, if someone else wanted this, they would be welcome to code it.

Quote:

For instance, your parser should be able to transform :
A OR B OR C -> A OR (B OR C) -> parsed into propel code.

AFAIK it should cope with both of those already, as they are logically equivalent.

Quote:

You may do this using recursive functions.

Yes, that's how the parser works: one can see how the recursion stores the "subclauses" by ticking the "Show parse tree" option.

[Updated on: Fri, 27 July 2007 14:11]


Remember Palestine
Re: Propel help for beginners - version 1 [message #32475 is a reply to message #30399 ] Sun, 29 July 2007 18:25 Go to previous messageGo to next message
Piwaï  is currently offline Piwaï
Messages: 40
Registered: July 2007
Member
Just a quick note : you forgot negative numbers Wink .

map_zone.X=0 AND map_zone.Y=-30 AND map_zone.ENABLED=1 LIMIT 1

-->

Error: The subclause 'map_zone.Y=-30' is not valid

It works putting 30 instead of -30.

Anyway, it's not really important since your tool is usefull to get the structure of the code.

Another small bug :
map_zone is parsed Map_zonePeer instead of MapZonePeer Wink.

Another thing to get the code more clear :

$c = new Criteria();
$crit0 = $c->getNewCriterion(Map_zonePeer::X, 0);
$crit1 = $c->getNewCriterion(Map_zonePeer::Y, 30);

// Perform AND at level 2 ($crit0 $crit1 )
$crit0->addAnd($crit1);
$crit2 = $c->getNewCriterion(Map_zonePeer::X, 0);
$crit3 = $c->getNewCriterion(Map_zonePeer::Y, 30);

// Perform AND at level 2 ($crit2 $crit3 )
$crit2->addAnd($crit3);
$crit4 = $c->getNewCriterion(Map_zonePeer::X, 0);
$crit5 = $c->getNewCriterion(Map_zonePeer::Y, 30);
...


Should be :

$c = new Criteria();

$crit0 = $c->getNewCriterion(Map_zonePeer::X, 0);
$crit1 = $c->getNewCriterion(Map_zonePeer::Y, 30);
// Perform AND at level 2 ($crit0 $crit1 )
$crit0->addAnd($crit1);

$crit2 = $c->getNewCriterion(Map_zonePeer::X, 0);
$crit3 = $c->getNewCriterion(Map_zonePeer::Y, 30);
// Perform AND at level 2 ($crit2 $crit3 )
$crit2->addAnd($crit3);

$crit4 = $c->getNewCriterion(Map_zonePeer::X, 0);
$crit5 = $c->getNewCriterion(Map_zonePeer::Y, 30);
...



It was my first time using it for a real purpose. Very useful Wink (note that the request I wrote to show the bug is not the one I wanted to parse, that would be too easy Razz)

[Updated on: Sun, 29 July 2007 18:35]

Re: Propel help for beginners - version 1 [message #32491 is a reply to message #30399 ] Mon, 30 July 2007 09:51 Go to previous messageGo to next message
halfer  is currently offline halfer
Messages: 9535
Registered: January 2006
Location: West Midlands, UK
Faithful Member
Thanks on raising the missing negative numbers capability: what a daft mistake Smile... it just needs a tweak to a regexp.

Meanwhile, you're right that the grouping of statements could be clearer; I'll add it to the list. Now all I need is some more time to work on it!


Remember Palestine
Re: Propel help for beginners - version 1 [message #32640 is a reply to message #30399 ] Wed, 01 August 2007 01:10 Go to previous messageGo to next message
halfer  is currently offline halfer
Messages: 9535
Registered: January 2006
Location: West Midlands, UK
Faithful Member
Thanks Piwaï - negative numbers issue should now be fixed.


Remember Palestine
Re: Propel help for beginners - version 1 [message #32668 is a reply to message #32640 ] Wed, 01 August 2007 11:13 Go to previous messageGo to next message
lajkonik86  is currently offline lajkonik86
Messages: 190
Registered: June 2007
Location: The Netherlands
Senior Member
interesting work halfer!
Re: Propel help for beginners - version 1 [message #32692 is a reply to message #30399 ] Wed, 01 August 2007 13:57 Go to previous messageGo to next message
Piwaï  is currently offline Piwaï
Messages: 40
Registered: July 2007
Member
Thanks for fixing it Wink .

What about this bug (maybe you didn't see it in my message) :

map_zone is parsed Map_zonePeer instead of MapZonePeer


Re: Propel help for beginners - version 1 [message #32696 is a reply to message #30399 ] Wed, 01 August 2007 14:41 Go to previous messageGo to next message
halfer  is currently offline halfer
Messages: 9535
Registered: January 2006
Location: West Midlands, UK
Faithful Member
It's in the list.


Remember Palestine
Re: Propel help for beginners - version 1 [message #32844 is a reply to message #30399 ] Fri, 03 August 2007 22:51 Go to previous messageGo to next message
halfer  is currently offline halfer
Messages: 9535
Registered: January 2006
Location: West Midlands, UK
Faithful Member
Some work has added: a choice to return arrays or ResultSet objects; a choice of ResultSets indexed by number or column name, and whether to include a demo loop in the code. Feel free to post bugs here.


Remember Palestine
Re: Propel help for beginners - version 1 [message #45433 is a reply to message #30399 ] Wed, 13 February 2008 11:14 Go to previous messageGo to next message
halfer  is currently offline halfer
Messages: 9535
Registered: January 2006
Location: West Midlands, UK
Faithful Member
I have fixed Piwaï's bug, and improved the internal string handling in the Criteria Generator. Could people when they get a moment have a click around, to see if I have introduced some new bugs? Wink I have some unit tests but I don't think they are particularly thorough.

I am not sure what to tackle next. I could do some easy ones (such as DISTINCT and LIMIT) but they probably aren't where people are getting stuck. I wonder whether I should consider 2-table joins (1:many) and after that, N-table joins, such as many:many?

One of these days I will add the code to the SVN repository (or if someone who knows how to do that, be my guest - the tarball is earlier in this thread). Perhaps that will encourage contributions from the community Smile

[Edit: I've just checked, as of today (13 Feb) the tarball is very out of date, I thought I'd fixed that. I recommend nothing is put into SVN for this until I sort that out, which will be ASAP].

[Updated on: Wed, 13 February 2008 11:19]


Remember Palestine
Re: Propel help for beginners - version 1 [message #45479 is a reply to message #45433 ] Thu, 14 February 2008 11:00 Go to previous messageGo to next message
snowkrash  is currently offline snowkrash
Messages: 518
Registered: July 2006
Location: The Netherlands
Faithful Member
Hi Halfer,

On a Windows system I suggest you use the TortoiseSVN client:
http://tortoisesvn.net/

Maybe the tutorial below can help:
http://www.shokhirev.com/nikolai/programs/SVN/svn.html#Step- by-step

Step 2 of the step-by-step guide applies to import a new piece of software.

But it depends on the policy of the repository-administrator on how to set your directory structure and where on the repository you can copy the software to.

That's probably the folks of the Symfony repository?
Re: Propel help for beginners - version 1 [message #45482 is a reply to message #30399 ] Thu, 14 February 2008 11:31 Go to previous messageGo to next message
halfer  is currently offline halfer
Messages: 9535
Registered: January 2006
Location: West Midlands, UK
Faithful Member
Thanks for that. Yes, I believe I have been given write access to the symfony /plugins SVN folder, just not tried it yet! I will give this a go soon.

Meanwhile, do let me know if you've any thoughts on the next bit of functionality Very Happy


Remember Palestine
Re: Propel help for beginners - version 1 [message #45483 is a reply to message #45482 ] Thu, 14 February 2008 11:39 Go to previous messageGo to next message
snowkrash  is currently offline snowkrash
Messages: 518
Registered: July 2006
Location: The Netherlands
Faithful Member
halfer wrote on Thu, 14 February 2008 11:31

Meanwhile, do let me know if you've any thoughts on the next bit of functionality Very Happy


Of course .... by the way, to my opinion, making joins is the most important feature. I use it all the time.

But maybe beginners se it otherwise Confused
Re: Propel help for beginners - version 1 [message #45486 is a reply to message #45482 ] Thu, 14 February 2008 12:04 Go to previous messageGo to next message
snowkrash  is currently offline snowkrash
Messages: 518
Registered: July 2006
Location: The Netherlands
Faithful Member
Hey Halfer ... I did a few tests:

- 1 -
    menu_item.TITEL IN ('Werkgever','Stagiair','Contact') 
AND pagina.META_TITLE<>'Aanmelden' 
AND pagina.MENU_ITEM_ID = menu_item.ID

The last AND subclause results in a is not valid.

- 2 -
    stagiair.IS_GELOCKED=0 
AND (
     (    regio_stagiair.REGIO_ID IN (26, 27, 30) 
      OR  opleiding_niveau_stagiair.OPLEIDING_NIVEAU_ID IN (14, 38, 56)
     ) 
      OR  opleiding_richting_stagiair.OPLEIDING_RICHTING_ID IN (45)
    )

The first IN subclause results in a is not valid.

These are valid WHERE conditions, since these are generated from the Propel Criteria system, so these should be correct.

There shouldn't be any copy/paste errors ... Very Happy
Re: Propel help for beginners - version 1 [message #45490 is a reply to message #30399 ] Thu, 14 February 2008 12:28 Go to previous messageGo to next message
halfer  is currently offline halfer
Messages: 9535
Registered: January 2006
Location: West Midlands, UK
Faithful Member
Thanks very much. This brings to light several bugs, although one item is not yet coded.

1 (a) I pasted your query just as it stands, and it turns out that the generator doesn't like the return characters - doh! That definitely needs to be fixed.

1. (b) Yes, the last clause will be invalid, as only strings and numbers are currently supported. I think this would be a good thing to work on next, as the criterions required are not obvious (requires Criteria::CUSTOM with custom comparison).

2. I simplified this statement to the smallest possible case that would fail, and got this:

regio_stagiair.REGIO_ID IN (26, 27, 30) OR opleiding_niveau_stagiair.OPLEIDING_NIVEAU_ID IN (14, 38, 56)

I believe this fails because, where an IN statement is found, the parser switches round brackets to square ones so that it can differentiate later on precedence brackets from IN brackets, but I think it could be switching the wrong ones. In fact if you use [] brackets for IN clauses (and remove all returns in the string) it will work Very Happy

Thanks for the reports, keep 'em coming.


Remember Palestine
Re: Propel help for beginners - version 1 [message #45696 is a reply to message #30399 ] Mon, 18 February 2008 10:19 Go to previous messageGo to next message
halfer  is currently offline halfer
Messages: 9535
Registered: January 2006
Location: West Midlands, UK
Faithful Member
I've fixed problem 2 - regex problem. Next step is to fix 1 (a)!


Remember Palestine
Re: Propel help for beginners - version 1 [message #45746 is a reply to message #45696 ] Mon, 18 February 2008 16:09 Go to previous messageGo to next message
snowkrash  is currently offline snowkrash
Messages: 518
Registered: July 2006
Location: The Netherlands
Faithful Member
Whow, you're fast. Surprised

Let's see what I can think of next Very Happy
Re: Propel help for beginners - version 1 [message #46057 is a reply to message #30399 ] Thu, 21 February 2008 15:40 Go to previous messageGo to next message
skyblaze  is currently offline skyblaze
Messages: 205
Registered: January 2008
Faithful Member
it is great but i think that with simple queries like for example:

select * from mytable where name = 'name1' and surname = 'surname1'

you should not use critorion objects 'cause it can be more simple with only the criteria object don't you think?
Re: Propel help for beginners - version 1 [message #46062 is a reply to message #30399 ] Thu, 21 February 2008 15:48 Go to previous messageGo to next message
halfer  is currently offline halfer
Messages: 9535
Registered: January 2006
Location: West Midlands, UK
Faithful Member
I agree, and it's already in the bug list! - down as "Use $c->addAnd() or $c->addOr() rather than Criterions for boolean expressions at the first level of the expression hierarchy".

It's not high priority though, so if you want to give it a go...


Remember Palestine
Re: Propel help for beginners - version 1 [message #46101 is a reply to message #46062 ] Thu, 21 February 2008 21:54 Go to previous messageGo to next message
skyblaze  is currently offline skyblaze
Messages: 205
Registered: January 2008
Faithful Member
halfer wrote on Thu, 21 February 2008 15:48

I agree, and it's already in the bug list! - down as "Use $c->addAnd() or $c->addOr() rather than Criterions for boolean expressions at the first level of the expression hierarchy".

It's not high priority though, so if you want to give it a go...

exuseme but if i only use $c->add, doesn't it produce a series of where clause conditions separated by AND operator? So for AND separated condition we don't even need the addAnd criteria object method right?
Re: Propel help for beginners - version 1 [message #46109 is a reply to message #46101 ] Fri, 22 February 2008 07:13 Go to previous messageGo to next message
snowkrash  is currently offline snowkrash
Messages: 518
Registered: July 2006
Location: The Netherlands
Faithful Member
Only when using one AND clause you can stick with add(). When combining AND clauses you have to chain the second, third etc AND clauses to the first --declared with add()-- with addAnd().

add() is a method is to just generate a general clause for WHERE. The Criteria class needs to know how you want to combine the next clause, so you have to specify this: AND with addAnd() or OR with addOr().
Re: Propel help for beginners - version 1 [message #50543 is a reply to message #30399 ] Mon, 21 April 2008 22:15 Go to previous messageGo to next message
Senhor  is currently offline Senhor
Messages: 1
Registered: April 2008
Junior Member
thumbs up!!!!!!
thx alot...

helped me alot to understand the hole Criteria thing...

Re: Propel help for beginners - version 1 [message #51313 is a reply to message #30399 ] Sat, 03 May 2008 17:29 Go to previous messageGo to next message
colklink  is currently offline colklink
Messages: 6
Registered: May 2007
Location: Chicago
Junior Member
I found this tool through Google and I gotta say - great job, thanks for the effort!
Re: Propel help for beginners - version 1 [message #51351 is a reply to message #30399 ] Sun, 04 May 2008 11:42 Go to previous messageGo to previous message
halfer  is currently offline halfer
Messages: 9535
Registered: January 2006
Location: West Midlands, UK
Faithful Member
You're welcome folks, glad it's useful Very Happy


Remember Palestine
Previous Topic:Developer needed for Symfony server migration and application development [Project-based]
Next Topic:What license should I choose?
Goto Forum:
  

powered by FUDforum - copyright ©2001-2004 FUD Forum Bulletin Board Software