There really are very few downfalls to running raw SQL other than that
you have to write it by hand and people tend to do it in a poor
fashion. There are lots of cases where raw SQL is by far the best way
to go about pulling data but there are a few things to remember. There
area few pitfalls though.
1: Custom queries DO NOT SANITIZE!!!!!!! I can't stress that enough.
Cake does a good job of protecting from SQL injection and because of
that, people forget that you have to do it sometimes. Just use the
Sanitize singleton and you should be fine.
2: Make sure that you make this a special function inside of your
model and that it's not just SQL code sitting in your controller. That
is both ugly and bad for MVC. I have some code that I have used to
extend the ability of the find method in my models so that it can do
stuff like $this->{$Model}->find('recent'); that I've used before with
queries to make for some very easy to use code.
3: Now that I've told you the rules go nuts and have fun! That said,
the containable behavior can do most of the hard query work that you
might be attempting so make sure that you try to use that before you
drop into raw SQL so that you can stay DB independent.
Here is the syntax
>From the model:
$results = $this->query("SELECT * FROM `TABLE` WHERE
`super_special_query_conditions` = 1');
They will come back in a reasonable format for you, but it will take
some tweaking. Good luck!
For more info on how to do a raw query check out the manual:
http://book.cakephp.org/view/456/query
Sincerely,
~Andrew Allen
On Dec 27, 2:09 pm, "Chad Casselman" <ccassel...@gmail.com> wrote:
> So does than mean I can't run MySQL commands beyonds selects or just for
> this one example? How do I do run MySQL commands in cakephp, or do I just
> run raw sqls?
>
> What is the downfall of running raw SQL?
>
> Chad
>
> On Sat, Dec 27, 2008 at 2:06 PM, Arthur Pemberton <pem...@gmail.com> wrote:
>
> > On Sat, Dec 27, 2008 at 12:39 PM, Chad Casselman <ccassel...@gmail.com>
> > wrote:
> > > Can someone explain this to me please. I need to under cakephp better in
> > > order to really leverage it.
>
> > > If I just run find all, then everything is fine I get data back in the
> > > format below which is fine; however, if I pass in fields (only need 3
> > fields
> > > from all that information) as in the previous email examples is says that
> > it
> > > doesn't know the table.field? Why is this?
>
> > > How do I get the information below, but create a custom column like
> > > DateDiff(Package.created, now()).
>
> > Would be easiest to just calculate the date diff in PHP.
>
> > > Array
> > > (
> > > [0] => Array
> > > (
> > > [Package] => Array
> > > (
>
> > > [id] => 2
> > > [created] => 2008-12-24 10:48:39
> > > [modified] => 2008-12-24 11:39:17
> > > [name] => Welcome A - 2 day
> > > [mailing_id] => 1
>
> > > [interval] => 2
> > > )
>
> > > [Mailing] => Array
> > > (
> > > [id] => 1
> > > [created] => 2008-12-24 10:47:40
> > > [modified] => 2008-12-24 10:47:40
>
> > > [name] => Welcome A
> > > [description] => Welcome Letter
> > > [weight] => 1.25
> > > [us_shipping] => .42
> > > [mex_shipping] => 1.23
>
> > > [int_shipping] => 4.50
> > > )
>
> > > [Sequence] => Array
> > > (
> > > [0] => Array
> > > (
> > > [id] => 2
>
> > > [created] => 2008-12-26 14:14:05
> > > [modified] => 2008-12-26 14:14:05
> > > [name] => Sequence Trial 2
> > > [PackagesSequence] => Array
>
> > > (
> > > [id] => 3
> > > [package_id] => 2
> > > [sequence_id] => 2
> > > )
>
> > > )
>
> > > )
>
> > > )
>
> > > )
>
> > > Thanks,
>
> > > Chad
>
> > > On Sat, Dec 27, 2008 at 11:18 AM, Webweave <webwe...@gmail.com> wrote:
>
> > >> Your original post showed 'uses' for the tables in question, which
> > >> infers that you don't have them linked to the controller's model.
>
> > >> If they are connected, you may not have recursive set properly to
> > >> cause Cake to include them. Cake needs to understand how the tables
> > >> get joined in order to build the SQL.
>
> > >> On Dec 27, 6:45 am, "Chad Casselman" <ccassel...@gmail.com> wrote:
> > >> > Even though the binding is in the models correctly, I have to
> > respecify
> > >> > the
> > >> > associations in this controller that are already in the models that
> > are
> > >> > being "used"? Assuming that that HABTM is not going to be a problem.
>
> > >> > Chad
>
> > >> > On Fri, Dec 26, 2008 at 11:11 PM, Webweave <webwe...@gmail.com>
> > wrote:
>
> > >> > > In order to fetch data from both tables, you need to join them, Cake
> > >> > > won't do this automagically.
>
> > >> > > On Dec 26, 5:40 pm, "Chad Casselman" <ccassel...@gmail.com> wrote:
> > >> > > > Should it bring in all hardcoded (model) associations when I
> > import
> > >> > > > them?
>
> > >> > > > Here is what I have and I can't pull any fields from Sequences or
> > >> > > > PackagesSequence objects/tables
>
> > >> > > > var $uses = array('Package','Sequence','PackagesSequence');
> > >> > > > $extra = array(
> > >> > > > 'recursive' => 1, //int
> > >> > > > 'fields' => array('Package.name',
> > 'Sequence.name'),
> > >> > > //array
> > >> > > > of field names
> > >> > > > );
>
> > >> > > > $this->Package->find('all', $extra);
>
> > >> > > > I get an error that it doesn't know the Sequence table. Thoughts?
> > >> > > > Do I
> > >> > > > have to recode the associations already in the models?
>
> > >> > > > Chad
>
> > >> > > > On Fri, Dec 26, 2008 at 6:26 PM, Arthur Pemberton <
> > pem...@gmail.com>
> > >> > > wrote:
>
> > >> > > > > On Fri, Dec 26, 2008 at 5:14 PM, Chad Casselman
> > >> > > > > <ccassel...@gmail.com>
> > >> > > > > wrote:
> > >> > > > > > I have a Controller for Jobs with a model Job.
>
> > >> > > > > > When I hit /jobs (the index function of Job Controller), I
> > need
> > >> > > > > > to
> > >> > > run a
> > >> > > > > > query on 2 other tables (not joined to Job) to find values to
> > >> > > manually
> > >> > > > > > insert into jobs before actually displaying the index.
>
> > >> > > > > > I have tried several things but can't seem to make any
> > progress
> > >> > > > > > on
> > >> > > this.
>
> > >> > > > > > Can anyone help me with this?
>
> > >> > > > > > 1) select fields from a join on customers and packages
> > (complex
> > >> > > > > conditions)
>
> > >> > > > > I believe you want to bind those models to each other [1]
>
> > >> > > > > > 2) iterate through results and insert/update values in job
>
> > >> > > > > Once you setup the appropriate bindings, you can do a
> > find('all')
> > >> > > > > on
> > >> > > > > them and iterate over the results of that. [2] may be clearer.
>
> > >> > > > > > 3) display jobs
>
> > >> > > > > I'm guessing that part already works with your index() action.
>
> > >> > > > > > Thank you in advance.
>
> > >> > > > > > Chad
>
> > >> > > > > [1]
>
> >http://book.cakephp.org/view/86/Creating-and-Destroying-Associations-.
> > >> > > ..
> > >> > > > > [2]http://book.cakephp.org/view/83/hasAndBelongsToMany-HABTM
>
> > >> > > > > --
> > >> > > > > Fedora 9 : sulphur is good for the skin
> > >> > > > > (www.pembo13.com)
>
> > --
> > Fedora 9 : sulphur is good for the skin
> > (www.pembo13.com)
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---
