Searchable relationship
Relationships on tables confuse me. Sometimes they work, sometimes they dont. Obivously i dont have both of these enabled at once, but why does the ViewColumn work fine, but the TextColumn gives an error. Working: Not working: with error
57 Replies
i also have the same question
From what I am seeing in discord, there are a lot of questions about relationship columns and searching/sorting them. Something we probably need to document better
Wonder if it’s be better to drop the dot syntax altogether and just have a ->relationship() on the column to eager load it. Seems like we have the reverse issue on forms where people try to use the dot syntax and it doesn’t work there.
Might give it a bit more flexibility too. idk
@pboivin any ideas why it works with the view column, but not the text column though? Shouldn’t they work the same?
Your text column isn’t a relationship. Should be the same for both.
the dot syntax is what eager loads the relationships for the record and column.
So with out the dot on the text column it’s just trying to load a field on the model instead of a relationship
But you can use any field on the searchable
But not if the relationship isn’t loaded.
So if you wanted to do that you’d have to modify the table query to ->with() the relationship.
Your text column isn’t a relationship. Should be the same for both.Yeah, my reply in the other thread was simplified but my use-case is similar to yours @Mark Chaney. I'm using a custom query for
searchable() and sortable() because the table column is computed (something like combining first + last name in formatStateUsing())I already had the relationship eager loaded in the table. I typically use with() on any big table
Sorry, on mobile, so not fully here. lol
Eager loading on the model won't help, as the search queries are separate dependent sub-queries on the related table(s).
The problem is that the code that builds the subquery for a related search, using orWhereRelation (and friends), is using the column name you give it verbatim. So in your case 'contact.name'. Which yields a query like ...
SELECT * FROM contacts WHERE [...] AND contact.name LIKE '%foo%'
... which obviously doesn't exist on the 'customers' table.
I think this could be fixed by stripping anything before the final dot in $caseAwareSearchColumn here ... but I'm not sure if this would break any other use cases.
https://github.com/filamentphp/filament/blob/3.x/packages/tables/src/Columns/Concerns/InteractsWithTableQuery.php#L114
GitHub
filament/packages/tables/src/Columns/Concerns/InteractsWithTableQue...
A collection of beautiful full-stack components for Laravel. The perfect starting point for your next app. Using Livewire, Alpine.js and Tailwind CSS. - filamentphp/filament
I yield to whatever Hugh says. Lol.
Hmm, I think this is further complicated by the fact that Mark's column is not dotted, so Filament won't even be building that dependent query.
That’s kinda what o was getting at. But still could be an internal issue.
My thought above would work if the column itself was dotted, and the additional search fields were on the same relation, like ...
TextColumn::make('contact.full_name')->searchable(['contact.first_name', 'contact.last_name'])
Which still breaks with the code as-is, but would work with the modification I suggested - I just tested it.Maybe there’s an underlying reason for it. But I still think that columns and forms should either both support or not support dot syntax. It’s confusing to end users. They should both operate the same unless there’s an explicit reason for them not doing it.
Having spent a month working on implementing deep nested relations in Filament core, I can see why tables and forms handle it differently. And changing it at this point would be a fucking nightmare.
I do get the complications of trying to implement search on a table though that has to be bound to an eloquent class.
But I also think that filament is really good at assuming things and if you need functionality that is above and beyond that there is a certain level of competency that understands the nuances of what is required for the task at hand.
Mark's issue is very corner case, though. Having the column itself not be a relation, and the searchable() fields being relations. It could probably be coded round in core, but tbh that's one of those "just write a custom query in searchable()" cases.
I don’t disagree
Yeah, we get so used to Filament basically being magic, when we hit something that doesn't "just work", it's kinda jarring. Till you stop and think about the logic under the hood that would be needed to do what you assumed it would do.
At this point of use case a virtual column could go a long way. At least in my opinion or understanding.
But that’s outside the general use case of filament.
All filament can do is make it possible.
I do think there’s room for improvement though in a unified approach of how relationships are defined.
Bu @Mark Chaney, to answer your question. The reason the first one works and the second one doesn't is that the first one, the column itself is a relation, 'contact.name', so Filament knows it needs to use orWhereRelation('contact', ...), and if you gave any additional searchable() fields there (without dotting them) it would work, and find those fields on that related table.
The second, the main column is not a relation ('contact_display_name'), so with the current logic, Filament never even tries to use orWhereRelation, it just looks for your additional fields on the main table. It would need a butt load of additional logic to figure out that you've used a relation in the additional fields, and build separate related subqueries for each one.
I think trying to shoehorn dotted notation in forms, or supplanting dotted relations on columns with form-like relationship() methods would be a buttload of backward compat breaking work, for very little gain. Yes, it's slightly confusing for newcomers. But it just means you have to grok that tables and forms use different paradigms for related data (for very good reasons).
Fair enough. Just think that telling users to use -> relationship() on forms and make::(‘blah.name’) on tables isn’t the best UX.
Especially when table columns can be form like fields.
But I get the caveats.
I totally agree. And maybe it wouldn't be too awful adding an alternate syntax on columns like TextColumn::make('foo')->rlationship('bar') instead of make('foo.bar').
But then how do you deal with distant relations? I have columns like make('dailyLog.aircraft.aircraftType.name'). To encapsulate that as a single relation(), I'd have to use HasManyDeep to add an aircraftType relation directly on dailyLog, so I could do TextColumn::make('name')->relationship('aircraftType') ... unless I used dotted notation in the relationship('dailyLog.aircraft.aircraftType') ... in which case the whole thing is moot, as I had to use dotted notation.
And that, in a nutshell, is why columns use dotted notation, because they can be arbitrarily deep relations, without having to create a direct relationship on the model ... which often requires using 3rd party packages.
But in forms, you are essentially never using that kind of arbitrary depth relation. All related data you are entering on the form is (almost by definition) part of a direct relation on the model itself. Which lends itself to the relationship() paradigm.
So, what’s the difference in a form deeply nested and a table deeply nested. The underlying query is the same. Isn’t it.
It’s still a relationship on the model / record.
At that point either way I think you’re going beyond the scope of automatic detection and would require manual intervention.
And is probably not the majority use case.
I guess my point was that you can put a column name as anything and i guess i made the wrong assumption that TextColumn::make('foo')->searchable(['whatever']) was the the same a TextColumn::make('whatever') ->searchable()
Yeah, sorry, we've gone off on a massive tangent. But it's a tangent I'm interested in. 🙂
But that's kind of my point. Very often in table usage, it's not a relationship on the model. It's a distant relationship. And to shoehorn it in to being a relationship on the model means installing Staudenmeir's HasManyDeep and creating a relationship just to display one column. I have tables which render upwards of a dozen deep relation columns. And not one single one of those do I ever need to enter on the main form for the model. It's different paradigms. Forms are entering data directly related to the model and essentially never need to handle distant relations (except perhaps as nested groups, but that's a different use case). Tables often need to display lots of distantly related data.
we have have had needs in forms for a levels deep on relationships and is kind of a pain. The hack of groups() while a workaround isnt intuitive and kind of dirty imho. With all of that said, i they should work in a similar fashion from the developers perspective. I like dot notation, its simple, but id rather have a consistent solution.
But consistency requires the same paradigm. Tables aren't forms, they serve a very different purpose.
That’s kinda what I’m trying to get at is that, why can’t dot notion on a layout component work with make::() in form the same way it works in a table column.
I think of them as targets per say. I want this data and you have to go through this to get to this to get to this. Doesnt matter if im fetching or saving
Seems to me they should both function the same way.
again, im only thinking about this as a person implementing it. Not as someone making the actual magic happen 😛
I also think about it from the dev standpoint. They are the ones implementing it.
Filament isn’t Wordpress where mom and pop are using it. It’s always about the dev.
oh, i meant devs like Dan that are smarter than me that dumb things down for people like me 🙂
I’m not smart enough to even be dumbed down too. Lol.
I started typing an explanation of why you need direct relations (actual relations on the model the form uses) in forms, and why trying to replace that with an arbitrary dotted chain would be a nightmare, but it'd take a short novel to explain.
ha. I think im more a pattern person that learns by example. Its not that i dont know how things work (well there is a lot i dont, but a lot i do as well). Which is why i get surprised at times why something works when based on patterns I have seen/used before, it should (again, in my opinion) work
Well, I don’t know the ins and outs, I’m just good at abusing them. That’s why I write plugins. 🤣😅
Long story short. Rendering a column, it doesn't matter how long the dotted chain is, or what relation type they are. You just recursively walk the chain, collecting records, and merge the end result. So it doesn't matter if it's a belongsTo chained to a HasMany chained to a MorphsMany chained to a BelongsTo. It boils down to a recursive call that walks along the dots, merges getRelatedRecords() for each relation in the chain, then collects the end result.
Now try and imagine how that works for WRITING data to a TextField:make('foo.bar.garply.wibble.wobble'), where any of those could be any type of relation.
So in summary, what is the best solution for allowing relationhip data to be sortable and searchable easily? 😛
LOL
even if its down only 1 level.
seems we can do for searchable(), not awful. But doesnt solve sorting
sorting really isnt as important to me
and im not against the above solution
In your specific case, if your 'contact_display_name' is really on the main model, not on the related contact model, and you need to search fields on the related model ... you'll need a custom query in the searchable() closure.
sorry, i gave a bad example. if i enter something into searchable(), i dont think that the column 'name' should even come into play
Out of interest, have you tried ...
using searchable(['contact.name']) shoudl be no different than TextColumn::make('contact.name')->searchable()
i have not, but damnit, i think I have indeed done that before
lol
Reading the code (I haven't tried it), if your main column name is dotted, then providing non-dotted search fields on that table should work. As it just takes thjose searchable() field names literally and stuffs them in the subquery on the related table.
makes sense
The takeaway here is that those searchable([...]) field names are field names that are just used exactly as you type them in the query, whether it's a related query (main column name is dotted) or not.
Filament does no processing of the searchable fields to see if you used a relationship, in order to replace (say) 'contact.name' (<relationship>.field) with the actual fully qualified 'contacts.name' (<table-name>.field).
oh dang, i have no idea why i didnt connect those dots. lol
So I suspect ['contacts.name','contacts.foo'] would work, IFF your column name was 'contact.something', as it would then be inserting those searchable() field names into a query on the contacts table. But also superfluous.
But using relation names in the searchable fields won't work period.
thank you so much for taking your time to explain all of this to me. It really clicks now
(well, unless your relation name is exactly the same as the table name)
Tables\Columns\TextColumn::make('user.first_name')
->label('user')
->searchable(['first_name','last_name'])
->formatStateUsing(fn (Domain $record): string => $record->user->first_name . ' ' . $record->user->last_name)