Need some help querying a quick entity. I have a

FoundItem

which “hasMany”

details

. The memento of the

FoundItem

brings back the

details

info so this works great…

var foundItem = getInstance( 'FoundItem' ).findOrFail( rc.id ).getMemento();

But how do I filter the details when getting a FoundItem? I only want the details with a status of

P

(also

NULL

, but keeping it simple for now). This still brings back all of the details.

var foundItem    = getInstance( 'FoundItem' )
				.findOrFail( rc.id )
				.whereHas( 'details', function( q ) {
					q.where( 'status', 'P' );
				} )
				.getMemento();

I’ve also tried changing the line

findOfFail( rc.id )

to

firstWhere( 'id', rc.id)

with the same results. Thoughts?

I think

orFail

executes the query, and the subsequent

whereHas

isn't too meaningful. maybe

whereID(rc.id).whereHas(...).first()

Good thought. That unfortunately brings the same results back. I’ve played around with the toSQL() method and both options (original that I posted and your suggestion) provide SQL that should be filtering on the status. Mine is doing it through a where, yours through an EXISTS in the where.

sometimes you can trace if your database has something like "trace inbound queries",

getInstance("...").retrieveQuery().selectRaw("0 as __someNameToHelpFindItInSixTrillionQueries__").getOrWhatever()

Hmm, not familiar with that. I am going to check out the debugger though, just reading about it in the docs.

I think you are mixing up two different concepts. By using whereHas you will use a search method for FoundItem which will get ALL FoundItems which have at least one detail with status ‘P’ if you use get(), or just the first one when using first(). If you are looking for a specific record, which I assume you do, but only want the details with status P (or no details at all if this status is not there, I think you better stick to your findOrFail method and define your own memento in your FoundItem by adding some mementifier mapper , e.g

mappers  : {
            "filteredDetails" : function( _, memento ) {
                return this.details().where("status", "P").asMemento();
            }
        }

You could also create a relation in your foundItem which ONLY returns items with status P, e.g

function detailsWithP(){
        return hasMany("Detail", "some_id").where("status","P")
    }

Just make sure your detailsWithP will be included in your memento. Reviewing I think it might even be easier to to it this way, and your filtered details will not only be available in your memento, but in any instance of your FoundItem

Ok, heading down the last path right now to see how that goes. I didn’t know you can throw the where onto the hasMany there.

I think all relations are just QuickBuilders (a inherited QBuilder) but not 100% sure if that’s technically correct. Actually your getDetails() method is just an onmissing method which cals details().get(). You can chain quickbuilder methods and qb methods on any relation I believe.

Wow, that’s it! That way worked.

You can even just add this chaining to the details() itself, if you never need the details with other statuses….

Yeah, that’s what I’m going to do. But I was able to bring back the details and detailsWithP to see the difference. Phew!

This concept is quite powerful. Took me a while to get used to

quick

because it is quite different from cborm.

Agreed! So the final question is, how do I filter on P or NULL? In normal SQL land, it’d be something like

COALESCE(fd.status, 'P') = 'P'

.

something like this

function detailsWithP(){
        return hasMany("Detail", "some_id").where( function( q ) {
            q.whereNull( "status")
            .orwhere( "status", "P" )
      }
    }

You can nest any qb function here

Ok, I was about to say that is what I just tried. It just times out if the Null is included. Hmm.

Maybe just try on the wereNull only to see if the combi fails. Something like this should work, but it takes some figuring out.

Ha, you’re right again. Works fine with NULL or with P individually, but not the combo.