So all you would need to do essentially is to have a $conditions
array that has your filters, such as:
$conditions = collect([]);
$conditions->push($request->only(['model_name', 'color', 'year', 'valueMin', 'valueMax']'));
This will make a collection
that contains only the key => value
pairs from your model, color, year, valueMin
and valueMax
properties (maybe from some form?)
Then you'd simply pass that collection over to your query, like so:
Vehicle::where(function($q) use ($conditions){
if ($conditions->has('color')) {
$q->where('color', $conditions->get('color'));
}
return $q;
})->whereHas('model', function($q) use ($conditions) {
return $q->where($conditions->toArray());
})->get();
This way you don't need to worry about hard coding your conditions in your query and you can have a never ending limit. You simply need to pass in an array with key value pairs when making direct comparisons.
This solution will allow for the dynamic property comparisons that you're looking for.
Just as an FYI, you can also specify those things as column attributes. For instance, I might have done:
.order_by(model.Entry.amount.desc())
This is handy since it avoids an import
, and you can use it on other places such as in a relation definition, etc.
For more information, you can refer this SQLAlchemy 1.4 Documentation
Best Answer
A typical fact table in a star schema contains foreign key references to all dimension tables, so usually there wouldn't be any need for custom join conditions - they are determined automatically from foreign key references.
For example a star schema with two fact tables would look like:
But suppose you want to reduce the boilerplate in any case. I'd create generators local to the dimension classes which configure themselves on a fact table:
in which case usage would be like:
But, there's a problem with that. Assuming the dimension columns you're adding are primary key columns, the mapper configuration is going to fail since a class needs to have its primary keys set up before the mapping is set up. So assuming we're using declarative (which you'll see below has a nice effect), to make this approach work we'd have to use the
instrument_declarative()
function instead of the standard metaclass:So then we'd do something along the lines of:
If you actually have a good reason for custom join conditions, as long as there's some pattern to how those conditions are created, you can generate that with your
add_dimension()
:But the final cool thing if you're on 2.6, is to turn
add_dimension
into a class decorator. Here's an example with everything cleaned up: