getRecords question

[Skellington]

Hello,
I've been using DataMgr primarily for loading data using load xml.
Which has been great but now I really want to see what more I can do
with it.

I started to play around with getRecords and I was wondering how do I
do a "like" statement?

<cfset Application.DataMgr =
CreateObject("component","com.sebtools.DataMgr_MYSQL").init("xdbs")>
<cfset objData = {hostname = "mega"} />
<cfset qClient = Application.DataMgr.getRecords("Clients",objData)>


I have been looking over the docs but I'm just now sure what to do.

Thanks,
Charles

[Steve Bryant]

Charles,

Non-equality operations are not quite as simple as equality
operations, but still pretty manageable I think.

Take a look at filters:
http://www.bryantwebconsulting.com/docs/datamgr/filters.cfm

So, if you want to find records from the "Clients" table where the
"ClientName" field is LIKE "A%":

<cfset aFilters = ArrayNew(1)>
<cfset sFilter = {field="ClientName",operator="LIKE",value="A%"}>
<cfset ArrayAppend(aFilters,sFilter)>

<cfset qClients =
Application.DataMgr.getRecords(tablename="Clients",filters=aFilters)>

Make sense?

Steve

P.S. Check out named filters in 2.5:
http://www.bryantwebconsulting.com/docs/datamgr/named-filters.cfm?version=2.5

[Skellington]

Steve, thank you very much. I got it to work.

So, if you don't mind me continuing on this thread with another
question. So I have a master clients table, and all of my other tables
use the key to join the client data with the collected data, for
example inventory data. I join on the client_id column in Clients and
App_Inventory.

How would I use getRecords with multiple tables and a join?

Thanks,
Charles

On Dec 28, 7:04 pm, Steve Bryant <sebto...@gmail.com> wrote:
> Charles,
>
> Non-equality operations are not quite as simple as equality
> operations, but still pretty manageable I think.
>
> Take a look at filters:http://www.bryantwebconsulting.com/docs/datamgr/filters.cfm
>
> So, if you want to find records from the "Clients" table where the
> "ClientName" field is LIKE "A%":
>
> <cfset aFilters = ArrayNew(1)>
> <cfset sFilter = {field="ClientName",operator="LIKE",value="A%"}>
> <cfset ArrayAppend(aFilters,sFilter)>
>
> <cfset qClients =
> Application.DataMgr.getRecords(tablename="Clients",filters=aFilters)>
>
> Make sense?
>
> Steve
>

> P.S. Check out named filters in 2.5:http://www.bryantwebconsulting.com/docs/datamgr/named-filters.cfm?ver...

[Steve Bryant]

Glad it worked.

Good question. DataMgr really keys off of one table for a given query.
From one perspective this is a limitation, but not so much as it would
seem.

One thing to check out are relation fields.
http://www.bryantwebconsulting.com/docs/datamgr/relation-fields.cfm

Label Relation fields are an especially good place to start.
http://www.bryantwebconsulting.com/docs/datamgr/labels.cfm

This would allow you to (for example) get the Client name in a query
against another table (without actually having to have a physical
field for it).

This is a very flexible and powerful way to go and definitely what I
would recommend in most cases.

You can also insert custom SQL into a query if you really want a join.
Unless you are already using features like relation fields, however,
this is likely not worth the trouble as you could just as easily write
a query in cfquery.
http://www.bryantwebconsulting.com/docs/datamgr/insert-custom-sql.cfm

Lastly, if you are using features like relation fields but you want to
write a mostly custom query, you can take a look at cf_DMQuery.
http://www.bryantwebconsulting.com/blog/index.cfm/2010/10/1/Introducing-CFDMQuery

Let me know how that works for you.

Steve

[Skellington]

Steve, thank for the response. I think I was looking to use Data
Manager in ways it was not created for. :-)

I was thinking if I could do joins and multiple tables then I could
build a Ad-Hoc query interface for my database tables and pass a
filter etc. to getRecords for a result. Similar to this app/javascript
http://ksistem.com/jquery/sqlbuilderdemo.htm . I would use SQLBuilder
but it's not designed for a dynamic database schema. Which DataMgr
gives me using loadXML.

Thanks,
Charles

On Dec 29, 9:07 am, Steve Bryant <sebto...@gmail.com> wrote:
> Glad it worked.
>
> Good question. DataMgr really keys off of one table for a given query.
> From one perspective this is a limitation, but not so much as it would
> seem.
>

> One thing to check out are relation fields.http://www.bryantwebconsulting.com/docs/datamgr/relation-fields.cfm
>
> Label Relation fields are an especially good place to start.http://www.bryantwebconsulting.com/docs/datamgr/labels.cfm

>
> This would allow you to (for example) get the Client name in a query
> against another table (without actually having to have a physical
> field for it).
>
> This is a very flexible and powerful way to go and definitely what I
> would recommend in most cases.
>
> You can also insert custom SQL into a query if you really want a join.
> Unless you are already using features like relation fields, however,
> this is likely not worth the trouble as you could just as easily write

> a query in cfquery.http://www.bryantwebconsulting.com/docs/datamgr/insert-custom-sql.cfm

>
> Lastly, if you are using features like relation fields but you want to

> write a mostly custom query, you can take a look at cf_DMQuery.http://www.bryantwebconsulting.com/blog/index.cfm/2010/10/1/Introduci...

[Steve Bryant]

Charles,

I just realized that I never responded to this.

You are right that Data your use-case is outside of what I had
considered, but it is an interesting one.

The cf_DMQuery tag, in combination with getSelectSQL() should be
somewhat useful here.
http://www.bryantwebconsulting.com/blog/index.cfm/2010/10/1/Introducing-CFDMQuery

I think a few things would be needed in order for DataMgr to really assist here.

1) A way for DataMgr to know how tables are joined together. I
actually have a syntax for that (perhaps suboptimal for your use,
we'll see) working in 2.5 that I haven't blogged about yet. I will try
to do that soon.
2) A way for DataMgr to return the FROM clause joining multiple tables.

Once you have that, you could do something like this (pseudo-code):
<cf_DMQuery name="qRecords">
<cf_DMSQL method="getSelectSQL" tablename="#Form.table1#"
fieldlist="#Form.fields1#">
<cf_DMSQL method="getSelectSQL" tablename="#Form.table2#"
fieldlist="#Form.fields2#">
<cf_DMSQL method="getFromSQL" tables="#form.tables#">
<cfloop ...>
<cf_DMSQL method="getFieldWhereSQL" tablename="#table#"
fieldname="#field#" value="#value#" operator="#operator#">
</cfloop>
</cf_DMQuery>

Except for the getFromSQL() portion, I think the above would work now.
The syntax for that might need to change slightly to actuallyy work,
but what do you think about the idea? Does that generally address what
you were thinking?

Thanks,

Steve

> --
> You received this message because you are subscribed to the Google Groups "DataMgr" group.
> To post to this group, send email to dat...@googlegroups.com.
> To unsubscribe from this group, send email to datamgr+u...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/datamgr?hl=en.
>
>

[Skellington]

Steve,
Thanks. I will check this out and see if I can make it fit. If I can
it might save me a lot of time.

As I get some more time, I'll let you know.

Thanks,
Charles

On Jan 8, 5:46 am, Steve Bryant <sebto...@gmail.com> wrote:
> Charles,
>
> I just realized that I never responded to this.
>
> You are right that Data your use-case is outside of what I had
> considered, but it is an interesting one.
>
> The cf_DMQuery tag, in combination with getSelectSQL() should be

> somewhat useful here.http://www.bryantwebconsulting.com/blog/index.cfm/2010/10/1/Introduci...

> >http://ksistem.com/jquery/sqlbuilderdemo.htm. I would use SQLBuilder