Does LLBL support full outer joins? I'm trying to create a series of standardized views (one per entity) with a standardized set of fields from two entities. The join between the entities is on the entity name. e.g.

ProjectEntity
-Id
-Name

MappingEntity
-EntityName
-OtherInfo

I want to end up with something that has Id, Name, OtherInfo as fields where there's been a predicate applied to ProjectEntity (name like ""Blah%") and there's been a predicate applied to the MappingEntity (EntityName="ProjectEntity"), but there's no relationship between the two.

ChicagoKiwi wrote:

Does LLBL support full outer joins? I'm trying to create a series of standardized views (one per entity) with a standardized set of fields from two entities. The join between the entities is on the entity name. e.g.

ProjectEntity
-Id
-Name

MappingEntity
-EntityName
-OtherInfo

I want to end up with something that has Id, Name, OtherInfo as fields where there's been a predicate applied to ProjectEntity (name like ""Blah%") and there's been a predicate applied to the MappingEntity (EntityName="ProjectEntity"), but there's no relationship between the two.

Hi, there. You can create custom relationships on arbitrary fields from within the designer. I would recommend using either the typed view or the typed list/dynamic lists functionality of the framework to generate your custom field set.

Jeff...

jeffreygg wrote:

Hi, there. You can create custom relationships on arbitrary fields from within the designer. I would recommend using either the typed view or the typed list/dynamic lists functionality of the framework to generate your custom field set.

Jeff...

My understanding is that those relationships can only be on fields, and the only way to get a field in an entity would be to add it to the underlying table (or worse, create a view over the table).

Two assumptions in there - not sure whether one or both are incorrect...

But there is a relation possible, or am I mistaken? (on name?-> Project.Name (FK) -- m:1 --> Mapping.EntityName (PK)

Otis wrote:

But there is a relation possible, or am I mistaken? (on name?-> Project.Name (FK) -- m:1 --> Mapping.EntityName (PK)

No - the relationship is on the _entity_name, i.e. there's an row in the Mapping table with a PK of "Project"

Relations are based on entity definitions, and thus not on data. Your semantic relations are not supported. (and also not possible in a single SQL statement)

Otis wrote:

Relations are based on entity definitions, and thus not on data. Your semantic relations are not supported. (and also not possible in a single SQL statement)

It is possible with a cross join:

select id, name, otherinfo
from project 
cross join mapping
where name like 'blah%'
and entityname = 'Project'

This gives me the results I want, and I'd like, if possible, to replicate this using LLBL, rather than having to resort to some other mechanism to get the data.

ChicagoKiwi wrote:

Otis wrote:

Relations are based on entity definitions, and thus not on data. Your semantic relations are not supported. (and also not possible in a single SQL statement)

It is possible with a cross join:

select id, name, otherinfo
from project 
cross join mapping
where name like 'blah%'

and entityname = 'Project'

This gives me the results I want, and I'd like, if possible, to replicate this using LLBL, rather than having to resort to some other mechanism to get the data.

Hmmm. Cross joins aren't implemented (will be in June) as they're rarely used. the point with crossjoins is that they produce an enormous amount of data and you then filter out just a little bit from them.

But I don't understand your query. Wasn't it the problem that the TABLE to join with was stored inside 'mapping' ? If not, you can of course join it via an inner join:

select id, name, otherinfo
from project 
inner join mapping
ON 1=1
where name like 'blah%'
and entityname = 'Project'

where ON 1=1 (or other always true filter) is added as a CustomFilter to the relation, when adding the relation (define one, doesn't matter if you don't use it in that fashion, you can create one in code if you like) and you specify true for EntityRelation.CustomFilterReplacesOnClause so that the filter has to replace the ON clause.