876 questions
0
votes
3
answers
101
views
Select a single row based on conditions in multiple rows
I am working on a project related to arthropod species identification.
Here is a simplified table where we list various arthropod species with their properties:
i_spID
t_property
t_species
...
- 13
0
votes
3
answers
92
views
MySQL multiple EXISTS statements causes query to hang
I am implementing an entity-attribute-value model to store nationwide property data that will be updated daily: 130 million properties, will probably store billions of values. We also want to search ...
- 1,466
1
vote
1
answer
54
views
Is it common practice to re-create database coupling rows when updating an aggregate?
Imagine we have a CRUD system with products. Each Product can have Attributes that represent properties of a product, like size, color, weight, etc. It's a typical EAV database case (Entity, Attribute,...
- 1,214
0
votes
1
answer
126
views
Storing product attributes of different types in a product aggregate
I use the EAV (Entity-Attribute-Value) pattern in an inventory management system I'm developing.
It's very comparable to a typical webshop framework like Magento.
Attributes have a data type, like ...
- 1,214
0
votes
0
answers
39
views
Output to Django template from EAV model
I can’t figure out how to display the value of the characteristics into a template from the model, where the characteristics are compared with the values. I got the value from the Product table, but ...
user23597190
-1
votes
1
answer
142
views
Create a Mysql trigger on Update, to update another row in the same table [duplicate]
i have a table named tblcustomfieldsvalues where i want to create a trigger for updating a row in the same table, this is the table :
Table screenshot
The goal is to create conditions like this :
if ...
0
votes
0
answers
61
views
mysql how to select the same column from 2 different rows
I've got a table like this:
I want to select like the result is something like this:
611766 | 20912 | gruppo-1_codice_articolo | 112509892 | field_5db85a4e1decf
Where the last 2 column are the ...
- 8,083
0
votes
0
answers
34
views
Best solution for a limited number of dynamic model fields in Django app
I'm using Django to build an app for outdoor professionals (educators, guides) to keep track of their experience. Basically a logbook but eventually with features like collaboration, mapping, and ...
0
votes
1
answer
242
views
Is Entity-Attribute-Value pattern the right approach to model this data?
To control the experimental setup user inputs the following table which defines unique parameter configurations, i.e. combinations of parameter values under which the experiments should be run. The ...
- 66
0
votes
1
answer
569
views
Trying to convince a coworker to NOT use entity-attribute-value (EAV)
They really like the flexibility of it, being able to make a database update and then allow your users to collect a new data point. I've sent them
https://tapoueh.org/blog/2018/03/database-...
- 13
1
vote
1
answer
206
views
PostgreSQL jsonb with dynamic attributes
I have a table products with ID and jsonb:
prod_id|attributes
1| {"3": 17, "4": 50, "5": 195}
And I have a table attributes, that contains the name and id for each ...
- 7,871
2
votes
1
answer
165
views
How to groupBy in postgres with jsonb column to mimic an EAV count table?
I have a jsonb column that looks like this:
Id
Data
1
{state: ["CA", "NY"], county:["Los Angeles"]}
2
{city: ["Kansas City"], zipCode: "12345"}
3
{...
- 2,234
0
votes
1
answer
480
views
REDCap SQL query filtering on Instances
In a REDCap (EAV table) project each record is a testing site.
Project is divided into two instruments. Instrument 1 will have information on the testing site (Address, DAG associated).
Instrument 2 ...
- 1
1
vote
1
answer
155
views
How to correctly index fields/groups of fields in a mysql database?
I really need advice on how to properly optimize mysql database queries. I'm afraid to spoil everything at the very beginning, which will turn out to be a big headache for me later.
There is a catalog ...
- 13
0
votes
1
answer
157
views
How to implement `left outer join` with additional matching condition, with `annotate()` or something else?
The transaction (entity) records have customized attributes in EAV format, so we are implementing a design pattern that assembles EAV data with the entities by a series of left outer join operations ...
- 2,113
0
votes
1
answer
167
views
In Django, how to make a join query with matching conditions in addition to the foreign key?
We have a hand-written SQL query for proof of concept and hope to implement the function with the Django framework.
Specifically, Django's QuerySet usually implements a join query by matching the ...
- 2,113
0
votes
0
answers
241
views
How to filter items by multiple attribures using eav model?
I am building online catalog using eav antipatern. I know this is bad pattern, but i need to be able to add properties at runtime, so i choosed this.Also i cant use solr, because i use hotsted ...
- 11
0
votes
0
answers
225
views
Attribute Value length Limit In Nifi
I have the requirement where I am converting the base64 Image content to attribute and after that I am preparing the payload including the base64 Image content. But the problem is while converting the ...
user19296047
1
vote
1
answer
63
views
wordpress phpadmin SQL database query to get results in columns
In my wordpress website I have multiple custom fields under post type 'home_decor', using the below query returns two field 'post id' and 'hd_product_description'. Currently, I am using this query to ...
- 23
0
votes
0
answers
839
views
Prisma [NextJS]: Many-to-many query is very slow
I have a nextJS project and I am using Prisma as an ORM.
The concerning database-tables look like this:
model cars_archive {
id Int @id @default(autoincrement())
...
...
0
votes
1
answer
395
views
MySQL - Wordpress > how to get posts by post_status / How to sum meta_values
Hello MySQL specialists,
I am working on a wordpress website referencing used vehicles.
I have a table called "wp_686591_postmeta" in which I have various data including the 'brand' and the '...
- 9
0
votes
2
answers
594
views
Indexes for custom filtered queries
I have a vehicle table with many rows. Every vehicle is stored with a name, and then additional characterizing attributes. Let's say
name PRIMARY KEY
attr_1
attr_2
attr_3
attr_4
etc...
The user can ...
- 2,583
0
votes
1
answer
148
views
How can I achieve pivot table output in MySQL?
If I have a MySQL table looking something like this:
How to write a mysql query to get output like this:
userid total_entries products_sold
------------------------------------------------...
- 53
0
votes
1
answer
75
views
SQL Server: Transpose rows to columns redux
I've been reading all of the transpose rows to columns links on the whole of Stack, but I can't quite get the code in those to work with my situation.
I have a device table
deviceID
DeviceName
...
- 1
-1
votes
2
answers
65
views
SQL query optimization taking lot of time in execution
We have two tables one is properties and another one is property meta when we are getting data from one table "properties" , query only take less then one second in execution but when we are ...
- 755
0
votes
1
answer
63
views
MySQL dynamic columns
Problem description
I have the following table (SampleTable) which contains dynamic columns and want to sort, search and paginate dynamic columns.
Note: The actual table has more rows than 6.
...
- 419
1
vote
1
answer
2k
views
How to avoid performance degradation when run query with cast in where clause?
I have a table with 2 varchar columns (name and value)
and I have such a query:
select * from attribute
where name = 'width' and cast( value as integer) > 12
This query works but I suppose there ...
- 37.1k
0
votes
1
answer
77
views
Optimize heavy Mysql query
I am working with a heavy query that sometimes takes more than 10 minutes to complete.
I would like to know if there is any optimization for this query and improve the process time.
SELECT
data....
- 1
0
votes
1
answer
72
views
how to avoid multiple joins in a single query in CodeIgniter
this is my code
$this->db->select('custome_module.id, custome_module.name, min(l1.nmark_completed) as call_waiter, min(l2.nmark_completed) as bill,min(l3.nmark_completed) as tray,min(l4....
user17087887
0
votes
1
answer
221
views
How to group data with SQL
How do I group my Timestream data?
The table looks simplified like this:
point_delivery_number | measure_name | time | value
---------------------------------------------------------...
- 14.9k
-1
votes
2
answers
118
views
MYSQL - Count Query Taking Time
I am running the following query and it's taking more than 12 sec on my local system.
SELECT DISTINCT
entity_id_0
FROM
(SELECT DISTINCT
c0_.entity_id AS entity_id_0,
c0_....
- 36
1
vote
1
answer
303
views
Converting EAVT table into SCD type 2
After a lot of research and head picking, I'm still unable to find a good/clean solution to convert an entity-attribute-value-timestamp table to an scd type 2 dimension.
Here's the issue:
I have a CRM ...
- 43
1
vote
3
answers
707
views
Slow Mysql query after inner joining 3 tables and adding indexes
I am finding my mysql query very slow, at 1 second according to my testing just from this query alone. I have tried adding indexes. But this doesnt seem to work well. i have attached my explain, ...
- 11
3
votes
2
answers
514
views
EAV model - How to restrict product properties?
I have the following structure of my database, implementing a simple EAV model (see pic):
My product has a type, which through the junction table restricts prop_names, available for this product. And ...
0
votes
1
answer
853
views
Wordpress WPDB SQL - How to query two meta_key
I am trying to query two meta keys at once using my code:
SELECT * FROM wp_postmeta
WHERE meta_key = "listing_location_enabled"
AND meta_value = "yes"
AND meta_key = "...
- 25
0
votes
0
answers
51
views
How best to structure database with multiple possible join tables
I'm trying to work out how best to structure some database tables so I can ensure my response entities are as coherent as possible.
Basically say I have employees and I have different documents, that ...
0
votes
1
answer
730
views
Magento-Backend eav Module
catalog_product_entity_int table we have one value column, can anyone help me to find the details that we are populating on the basis of which table.
as per my understanding that in the eav_attribute ...
- 5
0
votes
1
answer
266
views
Best way to search properties in MySQL
Hi have a table of users where we store some top level fields like userid, createdOn, email
And a second table called properties that stores a key value pair of additional properties. Like userid, key,...
- 565
1
vote
1
answer
302
views
Optimizing Group by performance for EAV databsae model
I am using the below query and it is currently taking 0.7 sec. But when a performance test was run with 200 concurrent users the query was taking more than 60 sec. I have defined required composite ...
- 344
1
vote
1
answer
9k
views
Database schema design for products depending on the variants and prices
We are in the process of designing a database for an e-commerce application and website. We reached an obstacle that has set us back in searching and experimenting but none of the solutions worked ...
- 41
0
votes
1
answer
472
views
I'm trying to design a database for products attributes (dimensions, weight, method of cleaning, etc..) and their variants (colors, sizes, etc..)
I am a beginner and this is my first real project.
The below image shows what I have been trying to reach. But it got way complicated so fast and confusing. So I'm asking if there are different ways ...
- 41
0
votes
1
answer
98
views
Why are entities such as 'order' in the eav_entity_type table if they are not proper EAV entities?
The eav_entity_type table contains 4 'proper' EAV entities (customer, customer_address, catalog_category, catalog_product), but also some entities that are not 'proper' EAV entities, in the sense that ...
- 1
0
votes
1
answer
43
views
SQL multiple left join as new columns performance
I have 3 tables :
account
id
name
1
Google
2
Apple
custom_field
id
name
1
Phone
2
Email
custom_field_submission
id
custom_field_id
entity_id
value
1
1
1
555-444-333
2
1
2
111-111-111
3
2
1
...
- 178
-4
votes
1
answer
328
views
Get all available attributes with values for special collection on EAV
I'm using this package for deploying EAV model.
As any EAV system I have different table for storing varchar, boolean, text and integer value in my database, and attributes table for storing attribute....
- 27
0
votes
0
answers
325
views
Errors getting started with django-eav2
I'm trying to make a simple app for users to create simple tests/questionnaires for each other, where they can specify which data type should apply to the answer for each question. To this end, I'm ...
- 598
0
votes
0
answers
23
views
EAV Query dynamic fields to column [duplicate]
I have the following tables:
Dataentry
---------
name
id
DataentryRows
----------
id
dataentry_id
Fields
----------
dataentry_id
name
id
DataentryValue
----------
id
value
field_id
dataentry_row_id
...
- 5,146
0
votes
0
answers
49
views
Table design，select Scalable attributes
There is a commodity table
table field：{
long id,
long commodityId,
long storeroomId,
VARCHAR batch_property
}
batch_property value is a json.
for example： {"a_property":"a&...
- 1
0
votes
0
answers
726
views
EAV: Adding Attributes dynamically to a JAVA Class in Spring Boot Hibernate
For my 'School Management Project' I am using Spring Boot REST, hibernate Data JPA and MySQL as Database.
I have one Student class which has 5 attributes.
I want to implement a feature which will ...
- 46
1
vote
0
answers
24
views
MySQL EAV Database: Indexes not being utilised for queries using OR with LEFT JOINs (Wordpress DB) [duplicate]
We have a query on a Wordpress site (EAV database) that is very slow because it's failing to use the relevant index. Here's a simplified version of the offending query:
SELECT p.ID FROM wp_posts p
...
- 1,743
0
votes
0
answers
350
views
Index a MySQL table to search for any columns combination
I am developing a website for car ads. The search is quite complicated as you can search for cars by up to 100 filters (make, model, engine, hp, color, year, 4X4, ..... ).
I have two models of how I ...
- 18.9k