I would like to provide a WHERE condition on an inner query by specifying innertable.id = outertable.id. However, MySQL (5.0.45) reports "Unknown column 'outertable.id' in 'where clause'". Is this type of query possible? The inner query is pivoting rows to columns using a GROUP BY. This could be entirely be performed in the outer query,...
I have a number of objects, each one have an arbitrary number of shared, and distinct property-value pairs (more specifically: files, and their related properties -such as width, and height values for images, album/artist/length for music files, etc). I'd like to be able to search for objects having specific property/values (such as: by ...
I need to design tables which stores all the (Attributes) metadata of Files i.e., FileName, Author, Title, CreatedDate ... and Custom Metadata ( Metadata added to files by Users) Before examining the tables we can not say what and how many custom tags have been added on Files. Custom Tags i.i.e, CustUseBy, CustSendBy etc. Now To store ...
Hypothetical situation. I want to populate/load a sales/order collection with every order where the grand_total is equal to the total_paid. I know I can use addFieldToFilter to filter by a specific value, but is it possible to use this method to filter by other database values. If not, is there any data access object in the Magento sys...
I am facing issues trying to write a query. (this is slightly modified from my previous question) My tables are laid out as follows: tblTicketIssues.TicketID tblTicketIssues.RequesterID tblPersonnelProfile.PersonnelID tblPersonnelProfile.FirstName tblPersonnelProfile.LastName tblTicketAttribute.TicketID tblTicketAttribute.Attr...
Standard EAV schema : One column for Entity ID, one for Attribute ID, one for Value ID. Historical EAV schema : Add an additional column(s) for times/date-ranges At run time, certain rows will be excluded. There may be 0, 1, or many rows returned per entity, per attribute. We only want the most recent value for each attribute remainin...
Hi guys , we have created a simple asp.net/sqlserver 2000 based Business Process Management tool in which we have a feature that user can create simple one column form and attached to the process and define rules and path. We save the dynamically created forms controls values in EAV design tables but as the transactions is increasing we...
Outside of the source code, is there any extensive documentation on the Magento ORM? I get the basics, and can usually dig through the Mage code base, litter it with Mage::Log calls and figure out something that works, but my efficiency would go way up if I had a high level overview of how the models are intended to be used. How do the...
I'm going to be working on an application that allows our client to define fields that will be shown to users in sort of a feedback/survey environment. For example, a pizzeria might have a field asking for your favorite topping, a bar might have your favorite brand of beer. The system needs to be able to handle letting our customers cr...
Is there any name for the following DB table design: Basically we have generic columns representing key/value pair. id | k1 | v1 | k2 | v2 | k3 | v3 | .... 1 | name | sam | last_name| smith | NULL | NULL | ... In my application, I have many tables that have only one row and I would like to merge them into a generic ...
It is safe to say that the EAV/CR database model is bad. That said, Question: What database model, technique, or pattern should be used to deal with "classes" of attributes describing e-commerce products which can be changed at run time? In a good E-commerce database, you will store classes of options (like TV resolution then have ...
Let's say we're having an application which should be able to store all kind of products. Each product has at least an ID and a Name but all other attributes can be defined by the user himself. E.g. He could create a productgroup Ipods which would contain attributes capacity and generation E.g. He could create a productgroup TShirts wi...
Using SQL Server, have a name value pair table. Each row is basically userid, contentid, sectionid, parameter, value. So there is data I want to display in a table such as user information. Each bit of information is in it's own row, sow how do I get it into a DataSet for use in a Repeater? Can I somehow merge the rows into one? So I can...
I am currently messing around with some stuff for an idea for a site - where I pretty much want to enable my users to create "Tables" which holds data and then allow them to query over this data (in a less geeky way than writing up SQL Queries and hopefully easier than using excel). My idea, so far, is to represent this in my database u...
If you have to create an application like - let's say a blog application, creating the database schema is relatively simple. You have to create some tables, tblPosts, tblAttachments, tblCommets, tblBlaBla… and that's it (ok, i know, that's a bit simplified but you understand what i mean). What if you have an application where you want ...
I’m uploading products via the Magento API and they are not showing up in the frontend. I have to go into the backend, open them up, change nothing, save the product and then it will appear. Any idea why? I assume the act of saving it in the back end, is saving some extra flags in the DB, I just don’t know what. ...
Let's say you're collecting insider info on upcoming superhero movie releases and your main Movie table looks something like this: Table 1 Title Director Leading Male Leading Female Villain -------------------------------------------------------------------------- Green Lantern Kubrick Robert Redford ...
I thought I would rewrite this question (same iteration). The original was how to wrap a repository pattern around an EAV/CR database. I am trying a different approach. Question: How could you code a data repository in a "factory" design pattern way? I have a fixed number of entities, but the attributes to these entities are fairly c...
I was recently exposed to the concept of an Entity Attribute Value database model via. the Magento e-commerce system. Varien (the company behind Magento) has used a relational database (mysql) to create an EAV style model using PHP Objects. I'm curious if there are "native" EAV database solutions out there. The "featured recently on ...
I've recently inherited a system that relies heavily on an EAV database structure, and is really struggling from a performance perspective. What I want to do is use nhibernate or another suitable ORM product to map these EAV tables to entities in such a way that we can map a row to a property. We can then refactor the database in order ...