Databases
StackOverflow2013
Postsdbo
POHQL Query - Castle Active Record - Outer Joins
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHQL Query - Castle Active Record - Outer Joins
    text
    copied!<p>I am trying to grab data from the DB of a particular table, where there is either no join to a another table, or there is but it isn't the right Data. </p> <p>Structure</p> <p>I have a domains table. All this does is hold a domain name, and a few other misc metadata. </p> <p>I have a features table, all this has is an ID column, and a column for a value of what that feature is. E.g. It could look like this : </p> <pre><code>1 | First Registered 2 | Expired On 3 | Hosted On </code></pre> <p>Etc. </p> <p>I have a DomainData table. This holds the values for the features. The columns are something like this. </p> <p>ID | DomainId | FeatureId | Value</p> <p>Essentially it holds the value of the feature for that domain, something like a key value column so that it can be ever expanding without modifying the table structure of the Domain table. </p> <p>Now, what I need to do is do a query for all domains that do not have feature X. </p> <p>For example, this works in SQL : </p> <pre><code>SELECT D.* FROM Domain AS D LEFT OUTER JOIN DomainData AS Data ON Data.DomainId = D.Id WHERE data.featureId IS NULL OR data.FeatureId != @FeatureId </code></pre> <p>That works fine in SQL, and returns all needed data. All that does is grab all domains that either have NO features at all, or they do have features, but not the ones I require. </p> <p>Now I am using CastleActiveRecord as my datalayer, but I am struggling to as to how I can write this in HQL. Spent many an hour on it now, and I either get back nothing, Or I get back ALL domains, regardless of their feature ID. Unfortunately I have deleted all the HQL statements that I have tried. </p> <p>Can I get some help on how I would rewrite the above statement to HQL?</p> <p>Side Note : Inside my classes, I have this inside the DomainData Class : </p> <pre><code> [BelongsTo("DomainId")] public Domain Domain { get; set; } [BelongsTo("FeatureId")] public Feature Feature { get; set; } </code></pre> <p>And this inside my Domain Class : </p> <pre><code>private IList&lt;DomainData&gt; featureData = new List&lt;DomainData&gt;(); [HasMany(typeof(DomainData), Table = "DomainData", ColumnKey = "DomainId")] public IList&lt;DomainData&gt; FeatureData { get { return featureData; } set { featureData = value; } } </code></pre> <p>I think that is the correct data structure? But correct me if I am wrong. It could be how I am doing the structure rather than the query itself. </p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload