Re: [transfer-dev] query of query timeout

[Mark Mandel]

How many publishers are you returning?

Mark

On Sat, Jul 12, 2008 at 8:07 AM, Loony2nz <Loon...@gmail.com> wrote:
>
> Hello all..
>
> I think I might have a bad transfer.xml configuration (i
> know..everyone gasp in unison hahaha).
>
> Here is a snippet of my transfer.xml:
>
> <package name="PUBLISHERS">
> <object name="PUBLISHERS" table="PUBLISHERS" >
> <id name="PUBLISHER_ID" type="string" generate="true"/>
> <property name="NAME" type="string" column="NAME"
> nullable="false"/>
>
> <manytomany name="ENDORSEMENTS" table="PUBLISHER_ENDORSEMENTS">
> <link to="PUBLISHERS.PUBLISHERS" column="PUBLISHER_ID"/>
> <link to="ENDORSEMENTS.ENDORSEMENTS" column="ENDORSEMENT_ID"/
>>
> <collection type="array">
> <order property="ENDORSEMENT_ID" order="asc"/>
> </collection>
> </manytomany>
>
> <onetomany name="MARKETPLACE_PUBLISHERS" lazy="true">
> <link to="MARKETPLACE_PUBLISHERS.MARKETPLACE_PUBLISHERS"
> column="PUBLISHER_ID"/>
> <collection type="array">
> <order property="DATE_LAST_UPDATED" order="desc"/>
> </collection>
> </onetomany>
> </object>
> </package>
> <package name="MARKETPLACE_PUBLISHERS">
> <object name="MARKETPLACE_PUBLISHERS" table="MARKETPLACE_PUBLISHERS"
>>
> <compositeid>
> <manytoone name="MARKETPLACES" />
> <manytoone name="PUBLISHERS" />
> </compositeid>
>
> <property name="PUBLISHER_ID" type="string" column="PUBLISHER_ID"
> nullable="false"/>
>
> <manytoone name="MARKETPLACES">
> <link to="MARKETPLACES.MARKETPLACES" column="MARKETPLACE_ID"/>
> </manytoone>
> <manytoone name="PUBLISHERS">
> <link to="PUBLISHERS.PUBLISHERS" column="PUBLISHER_ID"/>
> </manytoone>
> <manytoone name="STATUS_CODES">
> <link to="STATUS_CODES.STATUS_CODES" column="STATUS_CODE_ID"/>
> </manytoone>
> </object>
> </package>
>
> i'm trying to return all the records in marketplace_publishers based
> on a publisher ID.
>
> I've tried this:
> <cfset p =
> transfer.get('PUBLISHERS.PUBLISHERS',arguments.publisher_ID)>
> <cfset qGetAllPermissionsByPublisherID =
> p.GETMARKETPLACE_PUBLISHERSARRAY()>
>
> but I get a "query of query timeout" error with
> p.GETMARKETPLACE_PUBLISHERSARRAY().
>
> I've seen the queries generated in the debug screen and I tried
> running them in TOAD and I have to say, the queries are not returning
> the right data.
>
> Any help with debugging my transfer file?
>
> Thanks!
> >
>

--
E: mark....@gmail.com
W: www.compoundtheory.com

[Jon Messer]

You can't define relationships on both ends like that...

If PUBLISHERS is o2m MARKETPLACE_PUBLISHERS then you can't have MARKETPLACE_PUBLISHERS m2o PUBLISHERS

that'll cause a recursive infinite loop. I'm surprised you didn't get a syntax error or bomb the server.

try redefining like this :

  <object name="MARKETPLACE_PUBLISHERS" table="MARKETPLACE_PUBLISHERS" >
    <compositeid>
      <manytoone name="MARKETPLACES" />

      <parentonetomany class="PUBLISHERS.PUBLISHERS" />
    </compositeid>

    <manytoone name="MARKETPLACES">
      <link to="MARKETPLACES.MARKETPLACES" column="MARKETPLACE_ID"/>
    </manytoone>

    <manytoone name="STATUS_CODES">
      <link to="STATUS_CODES.STATUS_CODES" column="STATUS_CODE_ID"/>
    </manytoone>
  </object>

[Jennifer Larkin]

We actually have a lot of those but we are using lazy loading so they don't start showing up until we start calling the objects. This is why we are supposed to be looking at the joins and making sure that they only go one direction. Ahem.

--
I did not come here to get devoured by symbols of monarchy! -- Smirnov of the KGB, Casino Royale

Now blogging....
http://www.blivit.org/blog/index.cfm
http://www.blivit.org/mr_urc/index.cfm

[Mark Mandel]

The warnings are now big messageboxes.. so hopefully they won't be missed.

http://docs.transfer-orm.com/wiki/Transfer_Configuration_File.cfm

--
E: mark....@gmail.com
W: www.compoundtheory.com

[Loony2nz]

Jon,

That worked most excellently!

Just one more question. The method I'm using to return is
GETMARKETPLACE_PUBLISHERSARRAY().
And this returns 2 records where the publisher's ID is associated with
the marketplaces.

How can I return just 1 row that matches the publisherID and the
marketplaceID? I'm passing both variables as arguments to the
function.

Thanks!
Chris

On Jul 11, 3:57 pm, "Jon Messer" <sylvan.mes...@gmail.com> wrote:
> You can't define relationships on both ends like that...
>
> If PUBLISHERS is o2m MARKETPLACE_PUBLISHERS then you can't have
> MARKETPLACE_PUBLISHERS m2o PUBLISHERS
>
> that'll cause a recursive infinite loop. I'm surprised you didn't get a
> syntax error or bomb the server.
>
> try redefining like this :
>
> <object name="MARKETPLACE_PUBLISHERS" table="MARKETPLACE_PUBLISHERS" >
> <compositeid>
> <manytoone name="MARKETPLACES" />
> <parentonetomany class="PUBLISHERS.PUBLISHERS" />
> </compositeid>
> <manytoone name="MARKETPLACES">
> <link to="MARKETPLACES.MARKETPLACES" column="MARKETPLACE_ID"/>
> </manytoone>
> <manytoone name="STATUS_CODES">
> <link to="STATUS_CODES.STATUS_CODES" column="STATUS_CODE_ID"/>
> </manytoone>
> </object>
>

[Jon Messer]

It doesn't look like you are using decorators yet, if not I'd strongly suggest you look into them. They are the only way to use transfer and get beyond the basic relational CRUD paradigm.

So if you write a decorator for your PUBLISHERS object (btw did I say I think you should rename your objects into NormalCase ?)  you could write a method called something like getMarketPlaceById(marketPlaceId) that would loop over the array returned by GETMARKETPLACE_PUBLISHERSARRAY and return only the matching MarketPlace object if it exists.

So if it's an option, look into writing a decorator, all it is really is just a wrapper around the transfer object that you can customize with your own functions to implement your business logic...

[Loony2nz]

Thanks Jon. I'll give it a try on Monday when I get in the office.
My VPN isn't working now..ugh!!

On Jul 11, 10:27 pm, "Jon Messer" <sylvan.mes...@gmail.com> wrote:
> It doesn't look like you are using

> decorators<http://docs.transfer-orm.com/wiki/Writing_Decorators.cfm>yet,

> if not I'd strongly suggest you look into them. They are the only way
> to use transfer and get beyond the basic relational CRUD paradigm.
>
> So if you write a decorator for your PUBLISHERS object (btw did I say I
> think you should rename your objects into NormalCase ?) you could write a
> method called something like getMarketPlaceById(marketPlaceId) that would
> loop over the array returned by GETMARKETPLACE_PUBLISHERSARRAY and return
> only the matching MarketPlace object if it exists.
>
> So if it's an option, look into writing a decorator, all it is really is
> just a wrapper around the transfer object that you can customize with your
> own functions to implement your business logic...
>

[Loony2nz]

Jon and/or Mark,

I feel like such a n00b with this transfer thing, but I still need
help :(

My previous try to return data from transfer, returned it in a non-
Transfer object.
However, I need to return the data in a transfer object.

I've tried this with no luck:
<cfset var composite = StructNew()>
<cfset composite.marketplace_id = arguments.marketplaceID>
<cfset composite.publisher_id = arguments.publisherID>

<cfset publisher =
transfer.get('MARKETPLACE_PUBLISHERS.MARKETPLACE_PUBLISHERS',composite)>

When I try and cfdump any of the generated get methods, I get "0".

Here is my transfer.xml snippet:

<package name="MARKETPLACE_PUBLISHERS">
<object name="MARKETPLACE_PUBLISHERS" table="MARKETPLACE_PUBLISHERS" >
<compositeid>
<manytoone name="MARKETPLACES" />

<parentonetomany class="PUBLISHERS.PUBLISHERS" />
</compositeid>

<property name="STATUS_CODE_ID" type="numeric" column="STATUS_CODE_ID"

nullable="false"/>
<manytoone name="MARKETPLACES">
<link to="MARKETPLACES.MARKETPLACES" column="MARKETPLACE_ID"/>
</manytoone>
<manytoone name="PUBLISHERS">
<link to="PUBLISHERS.PUBLISHERS" column="PUBLISHER_ID"/>
</manytoone>

</object>
</package>

What am I missing? i know my issue lies in my transfer.xml file, but
I'm not sure where.

Thanks!

[Jon Messer]

You can't have both <manytoone name="PUBLISHERS"> and <parentonetomany class="PUBLISHERS.PUBLISHERS" /> defined in your MARKETPLACE_

PUBLISHERS object.

It is either the child of PUBLISHERS or it has a many to one PUBLISHERS it can't be both. Is that your current transfer config I thought you already fixed that (or am I thinking of someone else)?

Anyway to answer your question (maybe not depends on what you're trying to do), I think what you really want to do is this; from publisher iterate over the MARKETPLACE_PUBLISHERS array and work with the MARKETPLACE object  :

publisher = transfer.get('PUBLISHERS.PUBLISHERS',arguments.publisherID) ;
marketPlaceArray = publisher.getMARKETPLACE_PUBLISHERSArray();

for(i=1;i<arrayLen(marketPlaceArray);i++){
  if(marketPlaceArray[i].getMARKETPLACES().getMARKETPLACE_ID == arguments.marketplaceID){
    //do something interesting
  }
}


Does that make sense? Or did you actually mean to try to get the MARKETPLACE_PUBLISHERS object (or the MARKETPLACE object)? Maybe if you describe your use case a little more I could be more specific.

HTH

[Jennifer Larkin]

He needs to actually get the marketplace_publishers object so that he can update the status_code_id.

[Loony2nz]

Jon,

I am trying to get the Marketplace_publisher object returned as a
transfer object.

I've since changed my transfer.xml to look something like this:

<package name="MARKETPLACE_PUBLISHERS">
<object name="MARKETPLACE_PUBLISHERS" table="MARKETPLACE_PUBLISHERS" >
<compositeid>
<manytoone name="MARKETPLACES" />
<parentonetomany class="PUBLISHERS.PUBLISHERS" />
</compositeid>

<property name="MARKETPLACE_ID" type="numeric"
column="MARKETPLACE_ID" nullable="false"/>
<property name="PUBLISHER_ID" type="numeric" column="PUBLISHER_ID"
nullable="false"/>

<property name="STATUS_CODE_ID" type="numeric"
column="STATUS_CODE_ID" nullable="false"/>

<manytoone name="MARKETPLACES">
<link to="MARKETPLACES.MARKETPLACES" column="MARKETPLACE_ID"/>
</manytoone>

</object>
</package>

One publisher can be in many marketplaces.

I want to return (as a transfer object) the row with of my
MARKETPLACE_PUBLISHERS table that contains the compositeID of
MarketplaceID and PublisherID

MarketplaceID | Publisher ID | StatusCodeID
=================================
5 21 1
4 21 0
4 22 0

I am passing into my transfer.get method a structure with the key
value pairs of marketplaceid=5 and publisherid = 21. When I do
getCompositeID i get '0'

Your method above worked for me to loop over the array (thanks i
learned something from that for future use), however, I need to work
with a transfer object for my other functions.

::pulling hair out::

[Mark Mandel]

Is it the fact that your compositeid struct is not formed correctly?

Looking at your parent composition, the key should start with
'parent', which it does not.

http://docs.transfer-orm.com/wiki/Using_Composite_Keys.cfm#Object_Retrieval

For a parentonetomany composite id element, the key will be 'parent' +
the Object name of the class that is specified. (For example, if you
have a class of 'user.User', the Object name would be 'User'), and the
value is the foreign key that maps to this composite element on the
object.

Mark

--
E: mark....@gmail.com
W: www.compoundtheory.com

[Jon Messer]

I think Mark got you straightened out with the syntax, what I was suggesting was that you can get to the object you want from the publisher. That's why we set up these relationships, then to get that specific MARKETPLACE_PUBLISHERS object (and by extension that MARKETPLACE object) you could do what I suggested :

publisher = transfer.get('PUBLISHERS.PUBLISHERS',arguments.publisherID) ;

marketPlaceArray = publisher.getMARKETPLACE_PUBLISHERSArray();

for(i=1;i<arrayLen(marketPlaceArray);i++){

  if(marketPlaceArray[i].getMARKETPLACES().getMARKETPLACE_ID == arguments.marketPlaceID){
    return marketPlaceArray[i];
  }
}

true this is more verbose than the get with the composite key, but if this were part of a say deactivateMarketPlace(marketPlaceID) method on the publisher object, then you'd be putting more behavior and logic into the object and breaking away from the procedural habit of get data, change value, save data. (I chose deactivate as an example, I don't know what your "status" is meant to be)

Just a different way to skin that cat I guess. In the type of set up you are describing, I don't tend to think of the "join" object as an object that I would get directly or deal with directly.

Either I'd be dealing with the PUBLISHERS object (and get to the related MARKETPLACE,MARKETPLACE_PUBLISHERS objects via the relationship collection) or I'd be dealing with the MARKETPLACE and doing the inverse. YMMV.

Also I don't want to sound preachy but it might help you to start thinking of TransferObjects as "objects" that do things and not "rows" that are just data.

Hope that's helpful.

[Loony2nz]

Thanks Jon!

Yes, Mark got me on my way with syntax and I was able to retrieve the
record as I wanted!
:: no more hair pulling ::

Now that I have my object, I need to make changes to the object.
I've got all my setters correct. However, I'm having trouble writing
the data back to the DB.

I've tried using TQL

Pseudo code:

<cfsavecontent variable="tql">
update MARKETPLACE_PERMISSIONS.MARKETPLACE_PERMISSIONS
set MARKETPLACE_PERMISSIONS.MARKETPLACE_PERMISSIONS.STATUS_CODE_ID
= :STATUS_CODE_ID,

MARKETPLACE_PERMISSIONS.MARKETPLACE_PERMISSIONS.SIGNED_TERMS_AND_CONDITIONS
= :TERMSANDCONDITIONS,
MARKETPLACE_PERMISSIONS.MARKETPLACE_PERMISSIONS.DATE_LAST_UPDATED
= :LAST_UPDATE
where
MARKETPLACE_PERMISSIONS.MARKETPLACE_PERMISSIONS.MARKETPLACE_ID
= :MARKETPLACE_ID AND
MARKETPLACE_PERMISSIONS.MARKETPLACE_PERMISSIONS.PUBLISHER_ID
= :PUBLISHER_ID
</cfsavecontent>

transfer.createQuery(tql)

setParam("STATUS_CODE_ID", arguments.statusCodeID, "numeric")
setParam("TERMSANDCONDITIONS", arguments.signedTermsAndConditions,
"numeric")
setParam("MARKETPLACE_ID", arguments.marketplaceID, "numeric")
setParam("PUBLISHER_ID", arguments.publisherID, "numeric")
setParam("LAST_UPDATE", now(), "numeric")

variables.transfer.update(query)

My error is this:
The TRANSFER argument passed to the update function is not of type
TransferObject.

Ok..back to hair pulling :(

> On Mon, Jul 14, 2008 at 5:29 PM, Mark Mandel <mark.man...@gmail.com> wrote:
>
> > Is it the fact that your compositeid struct is not formed correctly?
>
> > Looking at your parent composition, the key should start with
> > 'parent', which it does not.
>

> >http://docs.transfer-orm.com/wiki/Using_Composite_Keys.cfm#Object_Ret...

>
> > For a parentonetomany composite id element, the key will be 'parent' +
> > the Object name of the class that is specified. (For example, if you
> > have a class of 'user.User', the Object name would be 'User'), and the
> > value is the foreign key that maps to this composite element on the
> > object.
>
> > Mark
>

> > E: mark.man...@gmail.com
> > W:www.compoundtheory.com

[Jon Messer]

That really is NOT how you should use transfer. I don't think it's even possible to do update queries with TQL, at least I've never seen anything like that and it's contrary to the whole concept of an ORM.

Transfer let's you work with the objects and let it will worry about saving.

So if you have your MARKETPLACE_PERMISSIONS object you'd do something like this :

mpObj = //whatever you do to get the object
mpObj.setTermsAndConditions(arguments.signedTermsAndConditions);
transfer.save(mpObj);

I would STRONGLY suggest you go through some of the getting started stuff on the transfer wiki. It sounds like you are not quite understanding the basics of what transfer is/does.

I don't say that to be rude and I hope you aren't offended but trying to do the update with TQL is a big red flag in my mind.

[Jon Messer]

Sorry that came of a little harsher than I intended.

Looking closer at your pseudo code I see that you weren't trying to actually run the query, just pass it to transfer.update, which expects you to pass it an object, not a query.

So if you just do  transfer.update(yourObjectRefHere); you should be ok, no query needed.

I still think it wouldn't hurt to walk through some of the getting started docs on the wiki.

[Brian Kotek]

Just save the object. You don't need to try to do this with TQL...in fact you can't. TQL is only for retrieving things. To save something, you have to save a Transfer Object. This is all explained in the documentation so I'd recommend that you look there first if you have questions of this sort. That's what it's there for, after all.