blog documentation FAQ followers forum marketplace services youtube

Php Queries w/ DataStore

The Jamroom Network » Forum » Jamroom Developers » Page » Php Queries w/ DataStore
nate
@nate
12 years ago
917 posts
I need to do a query of more than one field. For example, I need to query songs that have an audio genre that equals "Pop" and then I need to order them by play audio_stream_counts.

Can you show me how this is accomplished with dataStore objects?

Also is there any documentation or explanations of the rCore_db_search_items() function?
updated by @nate: 12/17/13 02:25:56PM
brian
@brian
12 years ago
10,148 posts
You can query and order by any number of fields (although the more fields you search and order on, the more the work has to be done by the DB, so performance can suffer on large data sets).

So for your example you could do something like this:

$_sc = array(
    "search" => array(
        "audio_genre = pop"
    ),
    "order_by" => array(
        "audio_stream_counts" => "numerical_desc"
    ),
    "pagebreak" => 12,
    "page"      => 1
);

$_rt = jrCore_db_search_items('jrAudio', $_sc);

So this query would return all songs that had the "audio_genre" key set to "pop", and it would order them by the value of the "audio_stream_counts" value, numerically descending.

Here's a more complicated example to show some of the things you can do:

$_sc = array(
    "search" => array(
        "audio_genre in pop,rock,dance",
        "audio_title like a% || audio_title like b%"
    ),
    "order_by" => array(
        "audio_stream_counts" => "numerical_desc",
        "audio_title" => "desc"
    ),
    "pagebreak" => 12,
    "page"      => 1
);

$_rt = jrCore_db_search_items('jrAudio', $_sc);

This example would find all audio entries with their audio_genre set to either "pop", "rock" or "dance", and their audio_title would start with either an "a" or a "b". It would then order them by the number of plays descending, and for entries that had the same number of plays it would order those by their title from z -> a.

Let me know if that helps.

Thanks!


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net
brian
@brian
12 years ago
10,148 posts
Just to add too that when you are doing your ordering, make sure and use "numerical_asc" or "numerical_desc" if the value you are ordering by is numeric - if you just use "asc" or "desc", then the results will be ordered lexicographically - i.e.

1
10
11
2
20
21
22
3
4
41
5
...

So just a heads up :)


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net
nate
@nate
12 years ago
917 posts
Awesome. I'm gonna email you on a personal matter.
Dazed
Dazed
@dazed
12 years ago
1,022 posts
Looks odd for an old oracle guy but I understand it hehe
updated by @dazed: 10/20/13 04:20:30PM
nate
@nate
12 years ago
917 posts
What exactly gets returned from the above code?
brian
@brian
12 years ago
10,148 posts
Dazed:
Looks odd for an old oracle guy but I understand it hehe

The idea is for the developer to not have to write any SQL ;)


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net
brian
@brian
12 years ago
10,148 posts
Natedogg265:
What exactly gets returned from the above code?

You'll get an array containing information about the results - it will have 3 sections:

- "_items" - this will contain an array of the actual items that were found (if any)
- "info" - this will contain information about the result set, i.e. page number, total number of pages, total number of results, etc.
- "params" - this will contain the parameters that were used to generate the result set (i.e. what you passed in and any info added by triggers).

Play around with it a bit and you'll see it's very consistent in what it returns.

Hope this helps!


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net
nate
@nate
12 years ago
917 posts
Can I do this?

$search[] = "_created BETWEEN {$_post['start_date']} AND {$_post['end_date']}"

$_sc = array( "search" => $search, ...

Most importantly, can I do BETWEEN?
updated by @nate: 10/20/13 07:26:29PM
brian
@brian
12 years ago
10,148 posts
Yes - since that is an "AND" condition, you would just use 2 searches - i.e.

"search" => array(
    "_created > $start_date"
    "_created < $end_date"
)

So multiple searches by default are AND'd together, so this gets you what you need.

Hope this helps!


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net
nate
@nate
12 years ago
917 posts
When I do this I get nothing
if ((isset($_post['start_date']) && strlen($_post['start_date'])) > 0 ) {
	$search[] = "_created > {$_post['start_date']}";
}
if (isset($_post['end_date']) && strlen($_post['end_date'])) {
	$search[] = "_created < {$_post['end_date']}";
}

if ( is_array( $_rt ) ) { ....

updated by @nate: 10/21/13 06:39:15AM
brian
@brian
12 years ago
10,148 posts
Have you verified that both start_date and end_date are epoch integers, and that there are items in the DS that fall between those 2 times?


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net
nate
@nate
12 years ago
917 posts
firebug:
end_date	1380603600000
genre	
max_price	
min_price	
start_date	1370062800000
P.S.
How do I use a code block on this forum?
updated by @nate: 10/21/13 06:38:51AM
brian
@brian
12 years ago
10,148 posts
The forum uses BBCode, so you can do:

[ code ]
.. code here ..
[ /code ]

so those are JS based epoch time stamps, which are based in milliseconds, not seconds. You need to divide javascript epoch times by 1000 to get seconds, and then it will match up to the DS.

Let me know if that helps.


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net
nate
@nate
12 years ago
917 posts
Items created 1378906878, 1378907105, 1378910757...
brian
@brian
12 years ago
10,148 posts
yeah those are 10 digit epoch time stamps based in seconds. Your Firebug output is showing epoch time in milliseconds.


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net
nate
@nate
12 years ago
917 posts
You know what... I knew that. Well... I recall reading it somewhere.

Thanks.
brian
@brian
12 years ago
10,148 posts
Natedogg265:
You know what... I knew that. Well... I recall reading it somewhere.

Thanks.

No problem ;)


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net

Tags